problem with join SQL Server 2000

Question

I have 3 tables - Items, Props, Items_To_Props

i need to return all items that match all properties that i send example

items
1 
2
3
4

props
T1
T2
T3

items_to_props
1 T1
1 T2
1 T3
2 T1
3 T1

when i send T1,T2 i need to get only item 1

Answer 1

It is correct that you only get one row for T2, you should get 3 for T1

Answer 2

SELECT T.itemId
  FROM (SELECT itemId, count(distinct prop) propCount
          FROM items_to_props
         WHERE prop in ('T1', 'T2')
      GROUP BY itemId) T
WHERE T.propCount = 2

If you don't know how many props you have, you can create a temp table #P(prop), populate it with your props and run the folowing query (will do the same thing):

SELECT T.itemId
  FROM (SELECT i.itemId, count(distinct p.prop) propCount
          FROM items_to_props i
          JOIN #P p on i.prop = p.prop
      GROUP BY i.itemId) T
WHERE T.propCount = (SELECT COUNT(DISTINCT prop) FROM #P)