Geek Answers Handbook

MySql JOIN table - result from one to many relationships

I have an e-commerce site (MySql / PHP) where I need to get some results on two joined tables. Here is a simplified version of what I have:

Table: product[
product_id,
other_irrelevant_stuff,
etc.
]

Table: product_to_category[
product_id,
category_id
]

Products can have several categories. I use product p LEFT JOIN product_to_category p2c ON (p2c.product_id = p.product_id). I need to get results for products that are assigned two specific categories.

Obviously, if I use p2c.category_id = 1 AND p2c.category_id = 2, I do not get any results, because there will be no separate position that will meet these criteria. If I use p2c.category_id = 1 OR p2c.category_id = 2, I get all the results from both categories (ALL category_id = 1and ALL category_id = 2) that I do not want. I want to get only those products that have BOTH category_id 1AND category_id 2.

, , , . - ?

+4
2

group by count distinct:

select p.product_id
from product p
    join product_to_category pc on p.product_id = pc.product_id
where pc.category_id in (1,2)
group by p.product_id
having count(distinct pc.category_id) = 2
+1

...

select distinct tab1.product_id

from product tab1

join product_to_category tab2 on tab1.product_id = tab2.product_id

where tab2.category_id in (1,2)

group by tab1.product_id;

, .

0

More articles:


All Articles