CODEWITHSUNDEEP
sqlpostgresql
Brain2xml
Brain2xml

Reputation: 67

Postgresql filter in relation

Table "Post":

id active
382857 true
382851 true
383015 true
382906 true
382909 true

Table "PosTags":

post_id tag_id
382857 480
382857 953
382857 9230
382857 9232
382857 56677
382857 107901

The first table stores posts, while the second one stores postags for each post. I need to gather information regarding posts and tags and I'm using the following query:

SELECT id 
FROM posts
WHERE id NOT IN (SELECT post_id FROM post_tags WHERE tag_id = 55723)
LIMIT 8

Problem is that this query won't retrieve posts that have no postags.

Can you help me fixing this query?

Upvotes: 0

Views: 403

Answers (1)

lemon
lemon

Reputation: 15482

Try with this query:

SELECT posts.id,
       posts.active
FROM      posts
LEFT JOIN posttags
       ON posts.id = posttags.post_id
      AND posttags.tag_id = 9230
WHERE posttags.post_id IS NULL

Basically it looks for the posts which have the specific tag_id you don't want and excludes them from the match of the posts table using a LEFT JOIN.

If you have more than one tag you want to exclude, it is sufficient to change the second part of the JOIN condition into the following:

AND posttags.tag_id IN [9230, ...]

SQL Fiddle here.

Upvotes: 1

Related Questions