Pls Help - Sql

[glence]

Problem

I have a table called

category that contains two fields account_number and category the table structure is given below

account_number cat_id
-------------------- ------
101 - 1
101 - 2
101 - 3

102 - 2
102 - 3
102 - 4

I need a query that returns the account number that contains category
ids 1,2,3

Yes I know we can write a query like

SELECT account_number from category where cat_id in(1,2,3)

but the above mentioned query returns account numbers 101 and 102 and this is not my requirement.

I need account numbers that contains all the three category ids

1,2,3, that is in the above mentioned case the output will be 101. And another example is, if we give input cat_id aS 2 and 4 theN the query must return 102, that is only account number 102 has got the category ids 2 and 4.

that is a query like

SELECT account_number from category where cat_id=1 and cat_id=2 and cat_id=3

but this is not returning any results.

please help

Rgds
Glence

[bad76]

Hi,
I think you have to join 3 time the same table:

SELECT c1.account_number 
from category c1,  category c2,  category c3
where c1.cat_id=1 and c2.cat_id=2 and c3.cat_id=3
    and c1.account_number=c2.account_number 
    and c1.account_number=c3.account_number

This give you the result you are searching for, but I don't know
if there is a better way to do it.

see you

[Lars_Berg]

 SELECT account_number 
from category 
where cat_id in(1,2,3) 
group by account_number
having count(distinct cat_id) = 3