remove some data from many to many result

Question

I have issue with my many to many database .

I have simple blog system

post | category | post_has_category

when I used join to select the posts has not in category "3" ,it's filed (3 is example entry )

because in my database store post category like this

post(id):1

category(id):3

post_has_category:

post_id category_id

1 3

1 4

after I select the post don'n in cat 3 , the post 1 is selected but I don't want it , because it's belongs to cat 3 in other row ..

Answer 1

We can use a subquery to identify all post_id in which a category_id of 3 appeared. Then, we can left join the original table to this subquery and retain only those post_id which did not match to any of the offending ones in the subquery.

SELECT p1.*
FROM posts p1
LEFT JOIN
(
    SELECT DISTINCT post_id
    FROM posts
    WHERE category_id = 3
) p2
    ON p1.post_id = p2.post_id
WHERE p2.post_id IS NULL

Note that DISTINCT in the subquery should be unnecessary if a given post_id can only be associated with a given category_id once.