0

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!
  • 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. – amaster Oct 6 '17 at 1:59
  • tags.id=1 cannot be AND tags.id=2 at the same time, can it? – mustaccio Oct 6 '17 at 2:30
  • @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. – amaster Oct 6 '17 at 2:33
  • 1
    @amaster i have posted a answer without needing to use multiple joins for every related tag.. – Raymond Nijland Oct 6 '17 at 14:30
  • 1
    FYI - In your statements, the LEFT JOINs will actually be INNER JOINs, because the WHERE clause requires that tags.id is not NULL. – RDFozz Oct 6 '17 at 16:56
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

0

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.