mySQL experts - need help with 'intersect'

I know that mySQL 5.x does not support INTERSECT, but that seems to be what I need.

Table A: Products (p_id)

Table B: Prod_cats (cat_id) - category info (name, description, etc)

Table C: prod_2cats (p_id, cat_id) - many to many

prod_2cats holds the many (1 or more) categories that have been assigned to Products (A).

Doing a query/filter lookup, (user interactive) and need to be able to select across multiple categ开发者_开发问答ories the products that meet ALL the criteria.

Ex: - 80 products assigned to Category X - 50 products assigned to Category Y - but only 10 products (intersect) are assigned to BOTH cat X AND cat Y

This sql works for one category:

SELECT * FROM products WHERE p_show='Y' AND p_id IN ( SELECT p_id FROM prods_2cats AS PC WHERE PC.cat_id =" . $cat_id ."

<-$cat_id is sanitized var passed from query form .

I can't seem to find the means to say ' give me the intersect of cat A and cat B' and get back the subset (10 records, from my example)

Help!

Hmm, I'm not sure that it's the best way to do it, but it can be added on to your already-existing query pretty easily:

SELECT *
FROM products
WHERE p_show='Y'
    AND p_id IN (SELECT p_id
                 FROM prods_2cats AS PC
                 WHERE PC.cat_id = $cat1_id)
    AND p_id IN (SELECT p_id
                 FROM prods_2cats AS PC2
                 WHERE PC2.cat_id = $cat2_id)

Apply the definition of intersection from set theory. So if X intersect Y gives set Z, then for each x in Z it holds that x is in X and x is in Y.

Something like

SELECT * FROM products WHERE p_show='Y' AND p_id IN 
( SELECT p_id FROM prods_2cats AS PC WHERE PC.cat_id =X) AND p_id IN
( SELECT p_id FROM prods_2cats AS PC WHERE PC.cat_id =Y)

SELECT *
FROM products
WHERE p_show='Y'
    AND p_id IN (SELECT p_id
                 FROM prods_2cats AS PC
                 WHERE PC.cat_id in ($cat1_id, $cat2_id ))

OR even better

SELECT p.*
FROM products p INNER JOIN prods_2cats AS PC on p.p_id = PC.p_id
WHERE p_show='Y' and C.cat_id in ($cat1_id, $cat2_id )

Hope this helps