mySQL experts - need help with 'intersect'

Question

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 categories 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!

Answer 1

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)

Answer 2

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)

Answer 3

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