Show records NOT in related table

[antifashionpimp]

Hi,

I have the following table structure:

tblPeople
---------
PeopleID 'primary key
Name


tblQuestionInst
---------------
InstID 'primary key
PeopleID 'foreign key to tblPeople, 1 to many relationship


tblQuestionInst contains records with PeopleID from tblPeople, and tblPeople contains all possible PeopleID's

I want a query that will display the PeopleID and Name of all records in tblPeople which are NOT in tblQuestionInst.

I tried the following SQL, but it gives me no results:

SELECT tblPeople.PeopleID, tblQuestionInst.PeopleID, tblPeople.Name
FROM tblPeople INNER JOIN tblQuestionInst ON tblPeople.PeopleID = tblQuestionInst.PeopleID
WHERE (((tblPeople.PeopleID) Not In (SELECT tblQuestionInst.PeopleID from tblQuestionInst)));

Somewhere my logic is not catching on
can somebody please help?

Thanks in advance

 

[KKilfoil]

Have you looked at the 'Find Unmatched Records' Query Wizard?

 

[antifashionpimp]

KKilfoil, you are the man! (or woman?)

when I checked it there were only 2 views!! Thanks for such a quick reply!

I did not know that the wizard could do this.

 

[neileg]

The wizard will certainly do the trick. But so as you understand how to do it from scratch, here's some info.

Your query uses an inner join. This will only return records where the PeopleID matches. If you use a left join, this will return all of the records in tblPeople and the corresponding records from tblQuestionInst. If you then add a WHERE to look for null records from tblQuestionInst, this will give you the records from tblPeople with no match in tblQuestionInst.

 

[antifashionpimp]

neileg,

Thanks, I checked the QBE design grid, and the SQL syntax for this query after I created it in the wizard. It all makes sense now.

Cheers!