How to create an SQL query that checks for duplicates in multiple tables

Question

I want to create an SQL query where I check if duplicates exist in several different tables. I do this in Teradata. I want the output to look like the following.

|Table A| |Table B| |Table C| |Table D| |Table D|

For each of these columns we can get the value Y or N.

(Y means that duplicates exist, N means that duplicates does not exist).

How do I create this script I have managed to write the code for how to check for duplicates in a a table:

SELECT  Customer_Id, Year_Month_Id, count(*)
FROM    A
GROUP BY 1,2
HAVING count(*)>1)

SQL is not a script language... When you say duplicates in multiple tables, do you mean within a table or between tables? — jarlh, Mar 13 '15 at 12:43
Ok, then I want to create an SQL query. I mean within a table. So the output detemines whether there are duplicates within the different tables. — Filip Eriksson, Mar 13 '15 at 13:03

Andrew · Accepted Answer · 2015-03-13T16:04:27.437

0

EDIT #2:

 SELECT
 *
 FROM

     (SELECT
    CASE WHEN MAX(cnt) >1 THEN 'Y' ELSE 'N' END AS [Table1]
        FROM
        (
        SELECT 
        customer_id,
        year_month_id,
        COUNT (*) AS Cnt
        FROM
        table1
        GROUP BY 1,2
        ) tbl1 
    ) t1
CROSS JOIN
     (SELECT
    CASE WHEN MAX(cnt) >1 THEN 'Y' ELSE 'N' END AS [Table2]
        FROM
        (
        SELECT 
        customer_id,
        year_month_id,
        COUNT (*) AS Cnt
        FROM
        table2
        GROUP BY 1,2
        ) tbl2
    ) t2

EDIT: For finding duplicates within a single table:

select
customer_id,
year_month_id,
case when cnt >1 then 'Y' else 'N' end
from
(
select 
customer_id,
year_month_id,
count (*) as Cnt
from
table1
group by 1,2
) t

If you're looking for duplicates between two tables, I'd probably use a union all, something like this:

 select
    customer_Id,
    year_month_id
    case when count(*) > 1 then 'Y' else 'N' end
    from
    (
    select distinct
    customer_id,
    year_month_id
    from
    table1
    group by 1,2
    UNION ALL
    select distinct
    customer_id,
    year_month_id
    from
    table2 )t 
    group by 1,2

edited Mar 13 '15 at 16:04

answered Mar 13 '15 at 14:27

Andrew

8,445
3
28
46

I am not looking for duplicates between two tables. I am looking for duplicates within the tables. – Filip Eriksson Mar 13 '15 at 14:37
Np. I am grateful for your help. This runs. However, I only want one row with either Y or N. This value should explain whether there duplicates in the table or not. How can I fix that? – Filip Eriksson Mar 13 '15 at 15:06
Even if I only try the first part before the cross join it still doesn't work. I get the errror message: Syntax error. expected something like an 'Except' keyword or a 'Minus' keyword between the word tbl1 and '.' – Filip Eriksson Mar 13 '15 at 16:00
This is strange since tbl1 is the last thing I am writing when I remove the cross join. Do you have any idea what the problem is? – Filip Eriksson Mar 13 '15 at 16:01
Left out closing parens and aliases, try it now. – Andrew Mar 13 '15 at 16:04

How to create an SQL query that checks for duplicates in multiple tables

1 Answers1