0

Unsure exactly how to put this question so apologies for the awkward phrasing, generally when I know how to properly describe a problem I can use the site search to look for it, or Google etc.

Anyway, simple enough issue - I'm querying a link / junction table with a variable array of AttributeIds, for example the IDs 14 and 17. Using the following table:

http://img220.imageshack.us/img220/5999/setattributecombo.gif

The only valid result I want to return from this query is where the ProductSetId is the same, so instead of 'IN' I want something like 

WHERE AttributeIds IN 14,17 AND ProductSetId is the same

In the above example, the only valid result would be 5 but if I use an IN query I get 2,5,7 as results.

Any ideas?

scaryjones
  • 119
  • 8
  • Please add the table you are referring to – Radu Caprescu Mar 08 '11 at 16:24
  • possible duplicate of [SQL query: Simulating an "AND" over several rows instead of sub-querying](http://stackoverflow.com/questions/163887/sql-query-simulating-an-and-over-several-rows-instead-of-sub-querying) – Amy B Mar 08 '11 at 16:24
  • The table is essentially as you see above with the exception of a primary key column, it's a junction table to only has the two columns plus primary key. I will read that possible duplicate, hopefully it's what I'm after. – scaryjones Mar 08 '11 at 16:27
  • What is the output if you also have a row {17,7}? – Jim Garrison Mar 08 '11 at 16:29
  • "is the same" is not good enough; you have to specify somehow that you want 5 and not 2 or 7 – Cosmin Mar 08 '11 at 16:30
  • @Cosmin: OP has written "In the above example, the only valid result would be 5 but if I use an IN query I get 2,5,7 as results.". Please read carefully. – shahkalpesh Mar 08 '11 at 16:31
  • @Jim Garrison - The output would then be 5,7 as there are two 'groups' that contain all those elements. – scaryjones Mar 08 '11 at 16:45

2 Answers2

1
SELECT ProductSetID, COUNT(*) AS CountOfMatchingRows
From MyTable
WHERE AttributeId IN (14,17 )
GROUP BY ProductSetID
HAVING COUNT(*) = 2
shahkalpesh
  • 33,172
  • 3
  • 63
  • 88
  • Perfect, thank you. Don't suppose anyone knows the name for this type of problem, or if it has a better name than how I described it in my question title? Thanks. – scaryjones Mar 08 '11 at 16:50
0

It sounds like you want to perform Relational Division. A good article on various methods is over at Simple-Talk. http://www.simple-talk.com/sql/t-sql-programming/divided-we-stand-the-sql-of-relational-division/

Shane Delmore
  • 1,575
  • 2
  • 13
  • 19