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

  • 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.


    Wednesday, July 13, 2011 10:53 AM

Answers

  • 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
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, July 13, 2011 12:52 PM

All replies

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

    Wednesday, July 13, 2011 12:18 PM
  • 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
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, July 13, 2011 12:52 PM