How to select rows that have multiple relations in common

Question

I apologize if this is a duplicate, I tried to search for an answer, but possibly do not have the right keywords to help my search.

Fiddle: http://sqlfiddle.com/#!9/ea5723/6

I have the 3 tables:

places

+----+---------+
| id | name |
+----+---------+
| 1 | Place A |
| 2 | Place B |
| 3 | Place C |
| 4 | Place D |
| 5 | Place E |
+----+---------+

tags

+----+-------+
| id | slug |
+----+-------+
| 1 | tag-z |
| 2 | tag-y |
| 3 | tag-x |
| 4 | tag-w |
| 5 | tag-v |
+----+-------+

place_taxonomy

+----------+--------+
| place_id | tag_id |
+----------+--------+
| 1 | 1 |
| 1 | 3 |
| 1 | 5 |
| 2 | 3 |
| 3 | 1 |
| 3 | 2 |
| 3 | 3 |
| 3 | 4 |
| 3 | 5 |
| 4 | 2 |
| 4 | 4 |
+----------+--------+

I have been able to query what places have a single tag with

SELECT
 places.id AS p_id,
 places.name
FROM
 places
 LEFT JOIN place_taxonomy
 ON places.id=place_taxonomy.place_id
 LEFT JOIN tags
 ON place_taxonomy.tag_id=tags.id
WHERE
 tags.id=1
GROUP BY
 p_id;

And I can select which places have either of multiple tags with:

SELECT
 places.id AS p_id,
 places.name
FROM
 places
 LEFT JOIN place_taxonomy
 ON places.id=place_taxonomy.place_id
 LEFT JOIN tags
 ON place_taxonomy.tag_id=tags.id
WHERE
 tags.id=1 OR
 tags.id=2
GROUP BY
 p_id;

But, the trouble I am having is how to select places that have a combination of tags such as tag-z and tag-y

SELECT
 places.id AS p_id,
 places.name
FROM
 places
 LEFT JOIN place_taxonomy
 ON places.id=place_taxonomy.place_id
 LEFT JOIN tags
 ON place_taxonomy.tag_id=tags.id
WHERE
 tags.id=1 AND
 tags.id=2
GROUP BY
 p_id;
#Returns No Results!

• amaster: I know this could be done by joining place_taxonomy and tags twice, but I am looking for a simpler solution as the query will be built with a varying number of compound tags. There may be up to 20 tags to query together to.find rows that have all 20+ tags.
• mustaccio: tags.id=1 cannot be AND tags.id=2 at the same time, can it?
• amaster: @mustaccio I know that is why the query returns an empty set, I am just wondering if there is a better answer than the one I posted which involved multiple joins for every related tag that is needed for comparison.
• Raymond Nijland: @amaster i have posted a answer without needing to use multiple joins for every related tag..
• RDFozz: FYI - In your statements, the LEFT JOINs will actually be INNER JOINs, because the WHERE clause requires that tags.id is not NULL.

Answer 1

But, the trouble I am having is how to select places that have a combination of tags such as tag-z and tag-y

You are close with your query. You can use GROUP BY and SUM to find those relations.

Please note mine query is valid with sql-mode enabled only_full_group_by because places.id is a PRIMARY key.

Query

SELECT
 places.id
 , places.name
FROM 
 places
INNER JOIN
 place_taxonomy
ON
 places.id = place_taxonomy.place_id
INNER JOIN 
 tags
ON
 place_taxonomy.tag_id = tags.id
GROUP BY 
 places.id 
HAVING
 SUM(tags.slug = 'tag-z')
 AND
 SUM(tags.slug = 'tag-y')

Result

 id name 
------ ---------
 3 Place C 

demo http://sqlfiddle.com/#!9/ea5723/15

Or use a delivered table to find the places with the tags 'tag-z' and 'tag-y'.

Query

SELECT
 places_taged.id 
 , places_taged.name 
FROM ( 
 SELECT
 places.id
 , places.name
 FROM 
 places
 INNER JOIN
 place_taxonomy
 ON
 places.id = place_taxonomy.place_id
 INNER JOIN 
 tags
 ON
 place_taxonomy.tag_id = tags.id
 WHERE
 tags.slug IN (
 'tag-z'
 , 'tag-y'
 )
)
 AS places_taged 
GROUP BY 
 places_taged.id 
 , places_taged.name 
HAVING COUNT(*) >= 2

Result

| id | name |
|----|---------|
| 3 | Place C |

demo http://sqlfiddle.com/#!9/ea5723/27

Answer 2

Fiddle: http://sqlfiddle.com/#!9/ea5723/9

Here is my plausible solution, but I think there might be a better answer out there. In this answer, I add multiple joins for every tag that needs to be compared. So if I am looking for 5 tags in common the query will have 10 join statements.

SELECT
 places.id AS p_id,
 places.name
FROM
 places
 JOIN place_taxonomy pt1
 ON places.id=pt1.place_id
 JOIN tags t1
 ON pt1.tag_id=t1.id AND t1.id=1
 JOIN place_taxonomy pt2
 ON places.id=pt2.place_id
 JOIN tags t2
 ON pt2.tag_id=t2.id AND t2.id=2
GROUP BY
 p_id;

The above query will give the response of Plan C which has both tags tag-z and tag-y

+------+--------+
| p_id | name |
+======+========+
| 3 | Plan C |
+------+--------+

Update: Alternatively if selecting tags by id and not any other column then we can remove the tags join. At least then we only have one additional join for every relationship checking.

SELECT
 places.id AS p_id,
 places.name
FROM
 places
 JOIN place_taxonomy pt1
 ON places.id=pt1.place_id AND pt1.tag_id=1
 JOIN place_taxonomy pt2
 ON places.id=pt2.place_id AND pt2.tag_id=2
GROUP BY
 p_id;