Select a field if mathes to 2 or more criteria

sakis · #1 (**permalink**) 11-17-09, 09:49

Hi

I have this db table

prod_id cat_id
1---------1
2---------1
3---------1
1---------2

I want the query to search for a product that belongs to 2 categories (having the cat_id's). In this case search for products that belongs to cat_id's 1 and 2.

in this example is prod_id=1 (belongs both to 1 and 2).

Is this possible to be done with a query ??

Plz help

dav1mo · #2 (**permalink**) 11-17-09, 11:05

Code:

select a.prod_id
   from ur_table a
where exists (select 1 from ur_table b
                   where a.prod_id <> b.prod_id
                       and a.cat_id = b.cat_id)

Dave

mike_bike_kite · #3 (**permalink**) 11-17-09, 11:36

I'm not sure the previous query will work (apologies if it does). Anyway something like the following might work better and be easier to expand if you need to match against 3 categories etc:

Code:

select   prod_id
from     ur_table
where    cat_id in ( 1,2 )
group by prod_id
having   count( distinct cat_id ) = 2;

Mike

sakis · #4 (**permalink**) 11-17-09, 16:45

Thanks a lot guys
I used Mike's solution and it works !!

Internet Services

Web Development

Digital Marketing

Consumer Tech