Simple SQL Select with all matches

Question

SQL question: I have table with two columns: ProductId and CatId

I want to select products that belong to all of the passed in CatIds.

For example this is my table:

ProductID        CatID
_____________________
1129                2
1129                3
1129                4
1130                2
1130                3

Now if I pass 2,3,4 the result should be 1129.

If I pass 2,3 the result should be 1130 (not 1129, as it should belong to all of the CatIds passed, no less - no more)

Any idea how this can be achieved? IN with AND can not work. IN with OR will not serve the purpose.

Thanks.

How are you passing in the ids to match? In a table valued parameter? — Martin Smith, Nov 13 '12 at 12:34
the key is to compare the results joined to you list with the results not joined to your list. getting the counts that match and are not zero. Now the trick is writing that query... — John Sobolewski, Nov 13 '12 at 12:35
no ... was trying with simple 'select' and 'where' clause with 'IN' ... not sure about table valued parameter thing (sorry not very good at sql at all :( )... — M. Ali Iftikhar, Nov 13 '12 at 12:37

score 3 · Accepted Answer · answered Nov 13 '12 at 12:38

3

SELECT ProductID
FROM Table1 a
WHERE CatID IN (2,3)
GROUP BY ProductID
HAVING COUNT(*) =
        (
          SELECT COUNT(*)
          FROM table1 b
          WHERE b.ProductID = a.ProductID
        ) AND 
        COUNT(*) = 2

SQLFiddle Demo (includes 2 queries)

answered Nov 13 '12 at 12:38

John Woo

258,903
69
498
492

I think this should work, thanks for the Fiddle Demo thing. but i did not quite get you last line : "COUNT(*) = 2" I can not hard code that ? it should be dynamic. What do you think? – M. Ali Iftikhar Nov 13 '12 at 12:45
2

@aDev - Then use your own abilities to adapt the answer to your own development constraints??? It's a bit unfair to criticise an answer for not meeting a constraint that you don't mention in the question... *[For example, insert the search parameters into a table variable, then use `WHERE CatID IN (SELECT id FROM @params)` and `AND COUNT(*) = (SELECT COUNT(*) FROM @params)`]* – MatBailie Nov 13 '12 at 12:47
1

@Dems ... no idea what you meant here ... i didn't criticized ... was just asking ... am sorry if it felt bad ... :) – M. Ali Iftikhar Nov 13 '12 at 12:53
hey ... got it working ... in my test thing ... but i think i can implement it now ... thanks every one ... :) – M. Ali Iftikhar Nov 13 '12 at 13:01

Maxim Krizhanovsky · Answer 2 · 2012-11-13T13:03:57.467

-1

The trick is to see to how many of the categories the product belongs, by grouping by productId and then using HAVING to filter results with smaller subset of the categories

SELECT ProductID, COUNT(*) as c FROM table WHERE CatID IN(2,3,4) GROUP BY ProductID HAVING c = 3

edited Nov 13 '12 at 13:03

answered Nov 13 '12 at 12:35

Maxim Krizhanovsky

26,265
5
59
89

i think this will output the `count` *not* the `id`. – John Woo Nov 13 '12 at 12:39
you can add the id to the query as well :) – Maxim Krizhanovsky Nov 13 '12 at 13:03

Simple SQL Select with all matches

2 Answers2