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:
我有一个电子商务网站(MySql / PHP),我需要在两个连接表中获得一些结果。这是我的简化版本:
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.
产品可能有多个类别。我正在使用产品p LEFT JOIN product_to_category p2c ON(p2c.product_id = p.product_id)。我需要为分配了两个特定类别的产品获取结果。
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
.
显然,如果我使用p2c.category_id = 1 AND p2c.category_id = 2,我没有结果,因为没有一个符合这些条件的订单项。如果我使用p2c.category_id = 1或者p2c.category_id = 2我得到了两个类别的所有结果(所有category_id = 1和所有category_id = 2)我不想要。我只想获得具有BOTH category_id 1和category_id 2的产品。
I'm usually pretty good at this, but maybe I'm just having a brain fart. Any ideas out there?
我通常都很擅长这一点,但也许我只是一个大脑放屁。有什么想法吗?
2 个解决方案
#1
1
This should work using group by
and count
with distinct
:
这应该使用group by和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
- SQL Fiddle Demo
SQL小提琴演示
#2
0
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.
试试这个,你的问题就会解决。
#1
1
This should work using group by
and count
with distinct
:
这应该使用group by和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
- SQL Fiddle Demo
SQL小提琴演示
#2
0
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.
试试这个,你的问题就会解决。