How can I make an SQL statement that finds unassociated records?
- by William Calleja
I have two tables as follows:
tblCountry (countryID, countryCode)
tblProjectCountry(ProjectID, countryID)
The tblCountry table is a list of all countries with their codes and the tblProjectCountry table associates certain countries with certain projects. I need an SQL statement that gives me a list of the countries with their country code that do NOT have an associated record in the tblProjectCountry table. so far I got to here:
SELECT tblCountry.countryID, tblCountry.countryCode
FROM tblProjectCountry INNER JOIN
tblCountry ON tblProjectCountry.countryID = tblCountry.countryID
WHERE (SELECT COUNT(ProjectID)
FROM tblProjectCountry
WHERE (ProjectID = 1) AND (countryID = tblCountry.countryID)) = 0
The above statement parses as correct but doesn't give the exact result I'm looking for. Can anyone help?