Wednesday, 28 August 2013

Postgres: Select where all rows with the same foreign key are null

Postgres: Select where all rows with the same foreign key are null

I have two tables posts and authors. An author has many posts, so there's
a foreign key posts.author_id. Post also has a column approved_at which is
a datetime, and is NULL until the post is approved
So how can I select all authors who have no approved posts?
I can select all authors who have at least one approved post with
something like this:
SELECT * FROM authors
WHERE id IN
(
SELECT a.id FROM authors a
JOIN posts p ON a.id = p.author_id
AND p.approved_at IS NOT NULL
)
But I can't figure out how to do the opposite: I want to select all
authors where all of their associated posts have approved_at = NULL. How
can I select this?

No comments:

Post a Comment