Re: Selecting several types

Danny (adler.danny@[EMAIL PROTECTED]
) writes:
> So I have these tables of items and item-types, and the items can be
> of several types:
> 
> Items:
> ID ; Name
> 1 ; Item1
> 2 ; Item2
> 
> ItemType:
> ID ; Type
> 100 ; Type1
> 200 ; Type2
> 300 ; Type3
> 
> Now, to link these 2 I have a "linking" table:
> 
> LinkingTable:
> ItemID ; ItemTypeID
> 1 ; 100
> 1 ; 200
> 2 ; 100
> 2 ; 300
> 
> Now I want to select all the items of type 100 or 200...
> 
> How can I do that without getting a duplicate row of item 1??
 
Another solution is to use EXISTS:

   SELECT i.item_id, i.item_name
   FROM   items i
   WHERE  EXISTS (SELECT *
                  FROM   linkingtable l
                  WHERE  i.item_id = l.item_id
                    AND  l.item_type_id IN (100, 200))



-- 
Erland Sommarskog, SQL Server MVP, esquel@[EMAIL PROTECTED]
 Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx