Given I have 3 tables and I need to retrieve all the rows where there is no relationship with a specific record and include the rows where there are zero relationships, how can I build this query?
x
SELECT id, name FROM books;
id | name
---------------------------
B1 | the book
B2 | super book
B3 | another great book
B4 | how to be a book
SELECT id, name FROM authors;
id | name
---------------------------
A1 | Huey
A2 | Dewey
A3 | Louie
A4 | Donald
SELECT "bookId", "authorId" FROM books_authors;
bookId | authorId
---------------------------+---------------------------
B1 | A1
B3 | A2
B4 | A2
B4 | A4
-- Book B2 has no author
-- Book B4 has 2 authors
I need all books where the author Dewey (id = A2) has no relation at all.
Because B4 has a relation with author Dewey (A2), it should not be returned.
The query should return only B1 and B2.
Advertisement
Answer
SELECT id
FROM books EXCEPT (
SELECT bookid FROM books_authors WHERE authorId='A2'
)