php - MySQL Select - 连接所有匹配的表

Question

我有一个包含 3 个表的数据库 - 产品、标签和产品标签。我需要一个查询返回具有所有指定标签的所有产品。换句话说，不要返回如果产品没有所有指定的标签，则该产品。

products
+----+-----+--------+------------------------+
| id | uid | name   | description            |
+----+-----+--------+------------------------+
| 1  | p1  | ball   | something that bounces |
+----+-----+--------+------------------------+
| 2  | p2  | block  | for building stuff     |
+----+-----+--------+------------------------+
| 3  | p3  | bucket | holds stuff            |
+----+-----+--------+------------------------+
| 4  | p4  | shovel | scoops stuff           |
+----+-----+--------+------------------------+


tags
+----+-----+--------+
| id | uid | name   |
+----+-----+--------+
| 1  | t1  | blue   |
+----+-----+--------+
| 2  | t2  | red    |
+----+-----+--------+
| 3  | t3  | green  |
+----+-----+--------+
| 4  | t4  | yellow |
+----+-----+--------+
| 5  | t5  | orange |
+----+-----+--------+


product_tags
+-------------+---------+
| product_uid | tag_uid |
+-------------+---------+
| p1          | t1      |
+-------------+---------+
| p1          | t2      |
+-------------+---------+
| p2          | t3      |
+-------------+---------+
| p2          | t4      |
+-------------+---------+
| p2          | t5      |
+-------------+---------+
| p3          | t1      |
+-------------+---------+
| p4          | t1      |
+-------------+---------+
| p4          | t5      |
+-------------+---------+

Here are some examples of results I'm looking for:

选择红色的产品 (t2):

+-------------+------+
| product_uid | name |
+-------------+------+
| p1          | ball |
+-------------+------+

选择所有红色和蓝色的产品(t2，t1):

+-------------+------+
| product_uid | name |
+-------------+------+
| p1          | ball |
+-------------+------+

选择所有红色、蓝色和黄色的产品(t2、t1、t4):

+--------------------+
|     NO PRODUCTS    |
+--------------------+

选择所有蓝色的产品 (t1):

+-------------+--------+
| product_uid | name   |
+-------------+--------+
| p1          | ball   |
+-------------+--------+
| p3          | bucket |
+-------------+--------+
| p4          | shovel |
+-------------+--------+

选择所有蓝色和橙色的产品(t1、t5):

+-------------+--------+
| product_uid | name   |
+-------------+--------+
| p4          | shovel |
+-------------+--------+

Here is a link to a SQLFiddle that's already set up. I tried a LEFT JOIN but it doesn't get me what I'm looking for.

http://sqlfiddle.com/#!9/795615/6

Answer 1

编辑:根据@spencer7593 的建议更新

假设您的标签是唯一的，您可以使用计数来仅按命中次数等于传递的标签的产品进行过滤。

这种方法的优点是允许无限标签作为输入。

SELECT products.uid
FROM product_tags
JOIN products ON product_tags.product_uid = products.uid
WHERE product_tags.tag_uid IN ('t2', 't1')
GROUP BY products.uid
HAVING COUNT(DISTINCT product_tags.tag_uid) = 2

或者对于 3 个标签:

SELECT products.uid
FROM product_tags
JOIN products ON product_tags.product_uid = products.uid
WHERE product_tags.tag_uid IN ('t3', 't2', 't1')
GROUP BY products.uid
HAVING COUNT(DISTINCT product_tags.tag_uid) = 3

等等……