conditional query question

videodead · #1 (**permalink**) 01-22-05, 18:48

I have the following table...

+----+-------------+----------+
| id | category_id | item_id |
+----+-------------+----------+
| 1 | 1 | 1 |
| 2 | 4 | 1 |
| 3 | 6 | 1 |
| 4 | 4 | 2 |
| 5 | 1 | 3 |
| 6 | 4 | 3 |
| 7 | 6 | 3 |
| 8 | 3 | 4 |
| 9 | 12 | 4 |
| 10 | 14 | 4 |
| 11 | 1 | 5 |
| 12 | 1 | 6 |
| 13 | 1 | 7 |
| 14 | 5 | 8 |
| 15 | 13 | 8 |
+----+-------------+----------+

I have a search that allows users to pick the categories they want to view items from but also to choose to not show items with other categories, is there any way in my query to stop an item from showing if it has both categories chosen, in other words, if the user wanted to view all the items from category 1 but not category 4 how could i make items 1 and 3 not show up in the results?

r937 · #2 (**permalink**) 01-23-05, 00:07

Code:

select item_id
  from catitems
group
    by item_id
having sum(case when category_id = 1 then 1 else 0 end) > 0 
   and sum(case when category_id = 4 then 1 else 0 end) = 0

results: item_ids 5,6,7

videodead · #3 (**permalink**) 01-23-05, 00:14

Works great, Thanks

Internet Services

Web Development

Digital Marketing

Consumer Tech