SQL - Filtering in many to many relationships

Question

I have an issue coming up with the correct query for filtering in a many-to-many relationship, I think it's best understood through example:

I have four tables: Post, PostTag, Tag, and User, where PostTag is the join table between Posts and Tags. The tags are User-specific - they belong to one user only and shouldn't be accessible by others.

I want to fetch the list of Posts with their Tags, but fetch only the Tags belonging to the logged in User.

SELECT *
    FROM post
    LEFT JOIN post_tag ON post.id = post_tag.post_id
    LEFT JOIN tag ON post_tag.tag_id = tag.id;

Above query returns all the Posts, but it also returns all of the Users tags, which is unwanted. My first instinct was to put it in a where query:

SELECT *
    FROM post
    LEFT JOIN post_tag ON post.id = post_tag.post_id
    LEFT JOIN tag ON post_tag.tag_id = tag.id
WHERE tag.user_id = :user_id;

But the above query basically turns the LEFT joins into INNER joins, and Posts without Tags do not get selected. So I tried putting it into JOIN condition:

SELECT *
    FROM post
    LEFT JOIN post_tag ON post.id = post_tag.post_id
    LEFT JOIN tag ON post_tag.tag_id = tag.id
        AND tag.user_id = :user_id;

The above does return all posts with only logged in user's tags, which is great, but I still have all the post_tags from other users which isn't ideal. So I found a way for excluding those with a simple WHERE:

SELECT *
    FROM post
    LEFT JOIN post_tag ON post.id = post_tag.post_id
    LEFT JOIN tag ON post_tag.tag_id = tag.id
        AND tag.user_id = :user_id
WHERE NOT (post_tag.id IS NOT NULL AND tag.id IS NULL)

The above query seems to work, but I'm not sure if it's the right and the simplest solution.

Answer 1

Alternative way, avoiding the post_tag junction-table in the result set. (I presume you are only interested in the fields from post and tag, and these already contain the post_id and tag_id columns, though they are both named id):

---

SELECT *
FROM post p
LEFT JOIN tag t 
        ON t.user_id = :user_id
        AND EXISTS (
                SELECT *
                FROM post_tag pt 
                WHERE pt.post_id = p.id
                AND  pt.tag_id = t.id
                )
        ;