How to select rows that have multiple relations in common 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!
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(*) &gt;= 2


Result

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


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