CODEWITHSUNDEEP
mysqlmany-to-many
kiamoz
kiamoz

Reputation: 714

remove some data from many to many result

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 ..

Upvotes: 0

Views: 24

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522211

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.

Upvotes: 1

Related Questions