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!

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;