Return tags associated with posts

Question

I use the query below to return all posts marked with the tag set:

SELECT imprensa_posts.slug AS slug, tipo, titulo, data
FROM imprensa_posts 
JOIN imprensa_tags_posts ON imprensa_tags_posts.id_post = imprensa_posts.id_post 
JOIN imprensa_tags ON imprensa_tags.id_tag = imprensa_tags_posts.id_tag 
WHERE imprensa_tags.slug = 'tag-4' 
ORDER BY data DESC

But I also need to get all the tags associated with each returned post. I tried this way:

SELECT imprensa_posts.slug AS slug, tipo, titulo, data,
GROUP_CONCAT(imprensa_tags.slug) AS tag_slugs, GROUP_CONCAT(tag) AS tags
FROM imprensa_posts 
JOIN imprensa_tags_posts ON imprensa_tags_posts.id_post = imprensa_posts.id_post 
JOIN imprensa_tags ON imprensa_tags.id_tag = imprensa_tags_posts.id_tag 
WHERE imprensa_tags.slug = 'tag-4' 
GROUP BY imprensa_posts.id_post
ORDER BY data DESC

But it doesn’t work. The query returns tag-4 for all posts.

UPDATE

I recreated the tables in SQL Fiddle: http://sqlfiddle.com/#! 9/3c04b/5

Answer 1

And if you do something like this

SELECT imprensa_posts.slug AS slug, tipo, titulo, data,
GROUP_CONCAT(imprensa_tags.slug) AS tag_slugs, GROUP_CONCAT(tag) AS tags
FROM imprensa_posts 
JOIN imprensa_tags_posts ON imprensa_tags_posts.id_post = imprensa_posts.id_post 
JOIN imprensa_tags ON imprensa_tags.id_tag = imprensa_tags_posts.id_tag 
WHERE EXISTS (SELECT * 
              FROM imprensa_tags it
              JOIN imprensa_tags_posts itp ON it.id_tag = itp.id_tag
              WHERE slug = 'tag-4' AND itp.id_post = imprensa_posts.id_post)
GROUP BY imprensa_posts.id_post
ORDER BY data DESC

The secret is in the EXISTS

EXISTS (SELECT * 
        FROM imprensa_tags it
        JOIN imprensa_tags_posts itp ON it.id_tag = itp.id_tag
        WHERE slug = 'tag-4' AND itp.id_post = imprensa_posts.id_post )

He will only return those who exist this tag related.

-

Another solution I thought

SELECT imprensa_posts.slug AS slug, tipo, titulo, data,
       (SELECT GROUP_CONCAT(slug) 
        FROM imprensa_tags it
        JOIN imprensa_tags_posts itp ON it.id_tag = itp.id_tag
        WHERE itp.id_post = imprensa_posts.id_post) AS tag_slugs,
       (SELECT GROUP_CONCAT(tag) 
        FROM imprensa_tags it
        JOIN imprensa_tags_posts itp ON it.id_tag = itp.id_tag
        WHERE itp.id_post = imprensa_posts.id_post) AS tags
FROM imprensa_posts 
JOIN imprensa_tags_posts ON imprensa_tags_posts.id_post = imprensa_posts.id_post 
JOIN imprensa_tags ON imprensa_tags.id_tag = imprensa_tags_posts.id_tag 
WHERE imprensa_tags.slug = 'tag-4'
GROUP BY imprensa_posts.id_post
ORDER BY data DESC