Query for data NOT in a table
-
Hi,
I am trying to select a set of records from one table that do not appear in a second table.
ie. Client Contacts have owners. The owners are held in a seperate table containing just the Contact ID and the owner ID. I need to run a report on all Contacts without an owner.
Bet it's easy but I'm a newbie. Any help appreciated
-
There are a number of ways to do this. My personal choice is using a LEFT JOIN and check for NULL condition.
SELECT
*
FROM
Clients C
LEFT JOIN
Owners O
ON
C.ContactID = O.ContactID
WHERE
O.ContactID IS NULL
-
This would do it. It runs a select for those contactids in the clientcontacts table that are not in the select of the contactids that have a matching contactid in the owners table.
SELECT ContactID
FROM ClientContacts
WHERE ContactID NOT IN (SELECT ContactID
FROM ClientContacts C
INNER JOIN Owners O
ON C.ContactID = O.ContactID)
-
Thanks everyone. I'll try these out on Monday. Knew it would be easy for the "Old Pro's"
-
Less of the "old" thankyou...
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply