MySQL Select - join tables that match all

user_377205 0
Viewed 66

I have a database with 3 tables - products, tags, & product_tags. I need a query that will return all of the products that have all of the specified tags. In other words, don't return the product if it does not have all of the specified tags.

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:

Select the products that are red (t2):

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

Select all products that are red & blue (t2, t1):

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

Select all products that are red, blue & yellow (t2, t1, t4):

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

Select all products that are blue (t1):

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

Select all products that are blue & orange (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

mysql php
2 Answers

EDIT: Updated with @spencer7593's suggestion

Assuming your tags are unique, you can use count to filter only by products that have a number of hits equal to the passed tags.

This approach has the advantage of allowing for unlimited tags as an input.

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

Or for 3 tags:

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

and so on...

user_85707260

try this:

select product_id,tag_uuid,uid,d.name,description,color from
(

select product_id,tag_uuid,b.name as color from
(select product_id,tag_uuid from product_tags) as a
LEFT JOIN
(select uid,name from tags) as b
on a.tag_uuid = b.uid

) as c
left JOIN
(select id,uid,name,description from products) as d
on c.product_id = d.uid


where color = 'blue'

just change the where if you need another color.

user_65642760
Related