SELECT Query - AND on 2 rows? 1

[wendyp]

I have a table like this:

id     cat_id
1        3
2        3
3        3
1        4
2        5

So an ID can have more than one category. I want to select records that are in both category 3 and 4.

If I make an SQL statement with OR, I'll get all the items for category 3 and category 4. But if I do an and, I'll get no records.

Do I need to make a temporary table with:

id     cats
1       3,4
2       3,5
3       3

Or do I run 2 queries, 1 for the first category and 1 for the second category and then find the duplicates?

What is the best way to do this?

Thanks,
/Wendy

 

[r937]

SELECT id
  FROM daTable
 WHERE cat_id IN ( 3 , 4 )
GROUP
    BY id
HAVING COUNT(*) = 2

r937.com | rudy.ca

 

[wendyp]

Beautiful! Thanks a million - I would have never thought of that. And I knew it had to be simple.