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.
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)
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 ?
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.
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?
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.