Get list of Records in one Table but not in another

Transact-SQL
1

I have two tables TblA and TblB. TblA contains a list of IDs and TblB contains a list of 43000 records. TblB has an ID field and the two tables are linked through the IDs.
I want to return a list of IDs which are in TblB but not in TblA.

Anyone know how to do this?

2 
SELECT ID FROM TblB
EXCEPT
SELECT ID FROM TblA;
3

This doesnt seem to be working for me. It seems that some records are not being accounted for.

Here is the code I am using:

SELECT Contact.ContactId
FROM Contact
EXCEPT
SELECT EnfProp.ApplicantID
FROM EnfProp

Maybe this is incorrect?

4

The query you posted will return all distinct ContactID's in Contact table except for those ContactIDs where there is a row in the EnfProp table with ApplicantID equal to the contactID from the Contact table.

You can see the behavior using this example. It will return one row = 3 (even though there are two rows with the value 3 in the first table). It does not return 5 even though that is in the second table.

Based on the description in your original post, that is the logic you were looking for. Or is it something else you are trying to get?

SELECT * FROM (VALUES (1),(2),(3),(3)) x(a)
EXCEPT
SELECT * FROM (VALUES (1),(2),(5)) y(b)
5

Thanks for the reply James.
When I do a select of the two tables joining them on contact.contactid = enfprop.applicantid
This select returns 1300 records out of a possible 10000 records.

My problem is that when I run the above code with the Except statement I am expecting it to return 10000 - 1300 = 8700 records
But when I run the Except statement code it returns 8900 records .. It's like it isn't picking up 200 records ?

Maybe it's due to the distinct but I have tried that but will try it again.

Other than it being the distinct causing a problem would you know what else could be causing my problem to occur ?

6

It could be that the join returns 1300 records because the relationship is not one-to-one. You can test that using the following:

SELECT c.Contact, COUNT(*)
FROM
	Contact c
	INNER JOIN EnfProp e ON 
		e.ApplicantID = c.ContactId
GROUP BY
	c.ContactId
HAVING 
	COUNT(*) > 1;

You can also see that if you run this query and see if there are any differences between the count and the count distinct

SELECT COUNT(ContactId), COUNT(DISTINCT contactId)
FROM Contact;

If there are no dups, the counts should be the same. Similarly look if there are any dups in the EnfProp table using

SELECT COUNT(ApplicantID), COUNT(DISTINCT ApplicantID)
FROM EnfProp;
7

Thanks James.
The Distinct doesn't appear to be my problem.

I think my problem is being caused by the fact that some records in EnfProp are not in Contact and this is skewing the data somewhat.
So basically all the records in EnfProp are not in Contact so should I use something other than the Except statement.
I suppose I want to return all records that are in Contact but not in EnfProp but I dont want to return the records that are in EnfProp but not in Contact either.

8

Hi,

here you go

select a.IDs
FROM TblB a
Left join TblA b ON(a.ID=b.ID)
WHERE b.ID IS NULL

Thanks
Subha

9

I am not following your description. Looking at the example that I posted earlier, which I am reproducing below, what is the output you are looking for?

SELECT * FROM (VALUES (1),(2),(3),(3)) x(a)
EXCEPT
SELECT * FROM (VALUES (1),(2),(5)) y(b)

The query as it is written will return a single row with value = 3. Is that what you want? Or do you need to get 5 also in the result? Or is it something else?

10

What you are telling me is fine.
It seems the data is quite a mess and so I thought I wasn't getting the correct results back but I am but they are just a mess.

Thanks for all your help.