Access SQL many to many query

Question

Hi, I have three tables:

Author(AID, Name)
Title(TID, Name)
AuthorOfTitle(ID, AID, TID)

My question is how do can I see only Authors that are connected to no titles, that is Authors where there is no record in AuthorOfTitle. How to do this in SQL. Btw am using MS Access 2007.

Answer 1

This ought to do it:

SELECT A.*
FROM Author A LEFT JOIN AuthorOfTitle T ON A.AID = T.AID
WHERE T.ID IS NULL

Answer 2

You can use the foo not in (select ... from bar) SQL subquery expression to do this.

SELECT AID, Name FROM Author 
WHERE Author.AID NOT IN (SELECT AuthorOfTitle.AID FROM AuthorOfTitle)