CODEWITHSUNDEEP
sqljoingroup-byduplicatesteradata
jahan
jahan

Reputation: 153

check for duplicates in sql

I am joining two tables A and B. My query looks something like this.

select id, name, sum(qty) 
from table 1 
left join table 2 on table1.id = table2.id and table2.column = XXXX
group by 1,2

I want to know if this join is creating any duplicated rows in table 1.

Im using teradata SQL

Upvotes: 2

Views: 1014

Answers (2)

Faris Kapo
Faris Kapo

Reputation: 366

One way to do it is perhaps something along the lines of 

select id, name, sum(qty) 
from table 1 as t1
left join table 2 as t2 on table1.id = table2.id and table2.column = XXXX
where t1.id in (select distinct id from table 1 )

Distinct checks for row distinction and you mentioned the only column where you need to check for duplicates is ID.

Upvotes: 0

GMB
GMB

Reputation: 222512

I understand that you want to know if more than one row may exist in table2 that matches your join conditions. Here is a query for that purpose:

select t2.id
from table2 t2
where 
    t2.colum = 'XXXX'
    and exists (select 1 from table1 t1 where t1.id = t2.id)
group by t2.id
having count(*) > 1

Any row returned by that query would duplicate the rows in your existing join.

Upvotes: 1

Related Questions