SQL Query exclusion for "set-within-sets"

Question

I have a table item_category with two columns: item_id, cat_id. Item to category is a many-to-many relationship.

If my table looks like this...

item_id | cat_id
1       | 1
1       | 2
2       | 3
2       | 4
3       | 5
3       | 6
4       | 7
4       | 8
5       | 9
5       | 10

... how can I select a distinct list of item_ids that do not have any rows where category_id is 2 or 7 (yielding item_ids of 2, 3, 5)?

Answer 1

I would do this using aggregation and a having clause:

select item_id
from item_category ic
group by item_id
having max(cat_id = 2) = 0 and
       max(cat_id = 7) = 0

This is an example of a "set-within-sets" query. Using group by with having is the most generalizable form for such a query. For instance, if you wanted to be sure that category 3 were included, you would change the having clause to:

having max(cat_id = 2) = 0 and
       max(cat_id = 7) = 0 and
       max(cat_id = 3) = 1

Answer 2

Try something like this :

SELECT DISTINCT item_id
FROM table_category
WHERE item_id NOT IN
      (  select distinct item_id 
           from item_category 
          where cat_id in (2,7)
      )