how to show a record which does not match.....

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

how to show a record which does not match.....

Author

Topic

barnsley
Starting Member

34 Posts

Posted - 2011-07-13 : 10:29:50

I would like to find out if there are duplicate records inside a table which don't appear in another table (NOTE: this is different to finding out if the records doesn't appear at all).

e.g.
tbl1:

ID | SURNAME
_____________
01 | Smith
02 | Jones
03 | Smith

tbl2:

ID | SURNAME
_____________
248 | Smith
249 | Parker
250 | McCall
251 | Ali
252 | Singh
253 | Jones

The SURNAME 'Smith' appears in the first table twice :-
I would like a query to tell me if any SURNAME appears in tbl1 more than it does in tbl2 (as they should appear the same amount of times).

Here's what i have so far, but this results in showing me both the records in tbl1.

SELECT tbl1.SURNAME, tbl2.SURNAME AS NewSurname
FROM tbl2 INNER JOIN
tbl1 ON tbl2.SURNAME = tbl1.SURNAME

results in:

SURNAME | NewSurname
____________________
Smith | Smith
Smith | Smith

So the result I am looking for is to see ONLY the erroneous record (in this case only 1 Smith)
NOTE: For ease of understanding I have substitted my field for surname but it's actually something more unique like a security number which i'm searching for.
thanks,

mark.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-07-13 : 10:36:46

If there are more than one, how do you know which is 'erroneous'.

I think it would be simplest to just count the occurences in each table, and then compare the counts.


Select isnull(a.surname,b.surname), aCnt, bCnt
From (Select surname, aCnt = count(*) From tbl1 Group By surname) A
Full Join (Select surname, bCnt = count(*) From tbl2 Group By surname) B
On A.surname = B.surname
Where isnull(aCnt,0) <> isnull(bCnt,0)

You could also do a Row_Number() and compare ordered entries... but i don't know how the order is relevant in your example.

Corey

I Has Returned!!

barnsley
Starting Member

34 Posts

Posted - 2011-07-13 : 11:10:14

quote:
Originally posted by Seventhnight

If there are more than one, how do you know which is 'erroneous'.

It wouldn't matter to me which is the erroneous record - just to know that there is one - is fine enough.

(although, if there were 2 'Smiths' and 2 'Parkers' - I'd still like to know that there was an erroneous 'Smith' AND 'Parker'.)

Thanks, I will try the above code

mark.

barnsley
Starting Member

34 Posts

Posted - 2011-07-13 : 12:15:04

thanks,
just tried the above code and it does count both columns successfuly.

I have saved this as a view and can reference this view like so:

SELECT Expr1
FROM vw.MYVIEW_Count
WHERE (bCnt > aCnt)

- I wonder if it's possible to incorporate this code:

WHERE (bCnt > aCnt)

into the above code - rather than reference the view (display it all in 1 go)?

thanks again.

mark.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-07-13 : 13:14:56

(bCnt > aCnt) - This isn't really a 'safe' condition as one, or both, could be null.

This should do it though:


Select surname = isnull(a.surname,b.surname), aCnt, bCnt
From (Select surname, aCnt = count(*) From tbl1 Group By surname) A
Full Join (Select surname, bCnt = count(*) From tbl2 Group By surname) B
On A.surname = B.surname
--Where isnull(aCnt,0) <> isnull(bCnt,0)
Where isnull(aCnt,0) < isnull(bCnt,0)

Corey

I Has Returned!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources