Subquery Help

[dar149]

I have two tables, tblCompanyInfo and tblContacts. I am trying to find all Companies that have a record in CompanyInfo but no record in Contacts. lngCompanyID should not exist in tblContacts. I came up with the following SQL to put in the criteria for lngCompanyID, but it's not working.

(SELECT [lngCompanyID] FROM [tblCompanyInfo] Where [tblCompanyInfo].[lngCompanyID] Not In (SELECT [tblContacts].[lngCompanyID]FROM [tblContacts]))

Any help would be appreciated...

 

[PHV]

In the SQL pane of the query window:
SELECT I.*
FROM tblCompanyInfo I LEFT JOIN tblContacts C ON I.lngCompanyID = C.lngCompanyID
WHERE C.lngCompanyID Is Null;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[dar149]

Thank you so much... It worked perfectly!