CODEWITHSUNDEEP
mysql
ArtPulse
ArtPulse

Reputation: 365

MySQL - Select by some "tags", include all tags

I've successfully managed to fetch articles filtering by matching tags in an AND manner.

This is my current code:

SELECT *
FROM articles a
JOIN article_tags a_t ON a_t.article_id = a.id
LEFT JOIN tags t ON t.id = a_t.tag_id
WHERE t.caption IN ('fire', 'water', 'earth')
HAVING COUNT(DISTINCT t.caption) = 3

Where:

Now The problem is that after matching, I want to retrieve all the tags that each article has. Even if they are matched by 3 different ones, one may have 5 tags, other 4 tags, and I want them included in each row. Something like "tag,tag,tag" or whatever I can parse, in some "tags" column.

Any ideas? I can't find a way around it...

Upvotes: 2

Views: 1160

Answers (1)

Barmar
Barmar

Reputation: 780724

You need to join your query as a subquery with a query that returns all the tags and combines them with GROUP_CONCAT().

select a.*, GROUP_CONCAT(DISTINCT t.caption) tags
from (select distinct a.*
      from articles a
      JOIN article_tags a_t on a_t.article_id = a.id
      JOIN tags t on t.id = a_t.tag_id
      WHERE t.caption IN ('fire', 'water', 'earth')
      GROUP BY a.id
      HAVING COUNT(DISTINCT t.caption) = 3) a
JOIN article_tags a_t on a_t.article_id = a.id
JOIN tags t on t.id = a_t.tag_id
GROUP BY a.id

BTW, there's no reason to use LEFT JOIN in your query, because you only care about rows with matches in tags.

I also wonder about the need for DISTINCT in the COUNT() -- do you really allow multiple tag IDs with the same caption?

Upvotes: 4

Related Questions