Search post by tag name, return post with all related tags in one query-CodePudding

I have three tables.

posts

post_id	post_content
1	Alpha
2	Beta

post_tags

post_id	tag_id
1	1
1	2
2	2

tags

tag_id	tag_name
1	Dogs
2	Cats

Is it possible in Postgres to make a query to return post 1 from posts and both of its tags if all I know is one of its tags.

I tried to accomplish this with the following query, with no success:

SELECT posts.post_id, posts.post_content, tags.tag_name
FROM posts
JOIN posts_tags
ON posts.post_id = post_tags.post_id 
JOIN tags
ON post_tags.tag_id = tags.tag_id
WHERE tags.tag_name = Dogs;

I am expecting to get the following result set

post_id	post_content	tag_name
1	Alpha	Dogs
1	Alpha	Cats

How should I modify my query to accomplish this result?

CodePudding user response：

Your query doesn't work because you filter directly on your joined table, thus picking only those records that satisfy the condition (WHERE tags.tag_name = Dogs).

Instead you could first find what are the "post_id"s that have that "tag_name", then filter the results of your join operations.

WITH cte AS (
    SELECT pt.post_id,
           p.post_content,
           t.tag_name
    FROM       posts p
    INNER JOIN post_tags pt
            ON p.post_id = pt.post_id
    INNER JOIN tags      t
            ON pt.tag_id = t.tag_id
)
SELECT post_id, post_content, tag_name
FROM       cte
INNER JOIN (SELECT post_id FROM cte WHERE tag_name = 'Dogs') dogs_tagged
        ON cte.post_id = dogs_tagged.post_id

Check the demo here.