CODEWITHSUNDEEP
sqlms-accessjoincompareduplicates
user1018766
user1018766

Reputation: 33

SQL: Compare 2 tables with duplicates

I need to compare 2 tables to find a duplicate, but the fields allow duplicates. 

For Example: 
Tbl1:
455
7991
7991

Tbl2: 
455
7991
7991
7991

I need to write a query that shows there is an extra 7991 in table 2. I tried a right join but it still finds the match so it doesn't work properly. If anyone could point me in the right direction i'd greatly appreciate it. Thanks!!

Upvotes: 3

Views: 2075

Answers (2)

Stuart Ainsworth
Stuart Ainsworth

Reputation: 12940

SELECT  t1.colname
FROM    ( SELECT    colname
                  , COUNT(colname) AS colCount
          FROM      tbl1
          GROUP BY  colname
        ) t1
        INNER JOIN ( SELECT   colname
                      , COUNT(colname) AS colCount
               FROM     tbl2
               GROUP BY colname
             ) t2 ON t1.colname = t2.colname
                     AND t1.colCount <> t2.colCount

Upvotes: 2

Mark Byers
Mark Byers

Reputation: 839074

If your database supports FULL OUTER JOIN (not MySQL) this will give you all the differences between the two tables:

SELECT
    T1.x,
    COALESCE(T1.cnt, 0) AS T1_count,
    COALESCE(T2.cnt, 0) AS T2_count
FROM
(
    SELECT x, COUNT(*) AS cnt
    FROM Tbl1
    GROUP BY x
) T1
FULL OUTER JOIN 
(
    SELECT x, COUNT(*) AS cnt
    FROM Tbl2
    GROUP BY x
) T2
ON T1.x = T2.x
WHERE T1.cnt <> T2.cnt

Upvotes: 1

Related Questions