how can I show duplicate records which appear inside 1 table which don't appear in another table

Question

User-938890548 posted

I would like to find out if there are duplicate records inside a table which only appear once in another table (NOTE: this is different to finding out if the record 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, so I'd like to see only 1 record).

NOTE: For ease of understanding I have substituted the field name for 'surname' but it's actually something more unique like a security number which i'm searching for, but the principle is exactly the same - as this could appear twice too.

thanks,

mark.

Answer 1

User-938890548 posted

think i've found the answer :

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)

------------

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

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

But I'm not sure how to incorporate this code:

WHERE (bCnt > aCnt)

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

Answer 2

User-843484705 posted

QUery Should look like

Select tbl2.surname,Dup.surname
From tbl2 inner join (Select surname From tbl1 Group By surname having count(*) > 1) DUp
on tbl2.surname = Dup.surname 

--- use you where clause