CODEWITHSUNDEEP
phpmysqlsql
Ralph Haynes
Ralph Haynes

Reputation: 83

MySql JOIN table results from one to many relationship

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

Table: product[
product_id,
other_irrelevant_stuff,
etc.
]

Table: product_to_category[
product_id,
category_id
]

Products may have multiple categories. I am using product p LEFT JOIN product_to_category p2c ON (p2c.product_id = p.product_id). I need to get results for products that have two particular categories assigned to them.

Obviously, if I use p2c.category_id = 1 AND p2c.category_id = 2 I get no results because there will not be a single line item that will match these criteria. If I use p2c.category_id = 1 OR p2c.category_id = 2 I get all results from both categories (ALL of category_id = 1 and ALL of category_id = 2) which I don't want. I only want to get products that have BOTH category_id 1 AND category_id 2.

I'm usually pretty good at this, but maybe I'm just having a brain fart. Any ideas out there?

Upvotes: 4

Views: 1503

Answers (2)

Abhik Dey
Abhik Dey

Reputation: 403

Just do the following...

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;

Try this, your problem will be solved.

Upvotes: 0

sgeddes
sgeddes

Reputation: 62831

This should work using group by and count with 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

Upvotes: 1

Related Questions