Skip to main content
added 809 characters in body
Source Link

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

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.

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

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

added 809 characters in body
Source Link

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.

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

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.

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

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.

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

Post Undeleted by Raymond Nijland
Post Deleted by Raymond Nijland
Source Link

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.

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