Remove duplicates after merge in SQL

Question

I have three tables (t1, t2, and t3). See below:

The first table:

ID          AMOUNT
1              100
2              50

The second table:

ID          TYPE
1              A
2              B

The third table:

TYPE          GRADE
A              X
B              Y
B              Z

We first did an inner join t1 and t2 by ID, and then inner join t2 and t3 by TYPE.

SELECT 
t1.ID as ID, 
t1.AMOUNT as AMOUNT, 
t2.TYPE as TYPE,
t3.GRADE as GRADE
FROM t1 
INNER JOIN t1 ON t1.ID=t2.ID
INNER JOIN t2 ON t2.TYPE=t3.TYPE

The final table

ID          AMOUNT          TYPE          GRADE
1              100           A            X   
2              50            B            Y 
2              50            B            Z

I need to check duplicates by ID and TYPE, and if the total records > 1, I need to remove the ones with GRADE = Z.

So the final desired output should be

ID          AMOUNT          TYPE          GRADE
1              100           A            X   
2              50            B            Y 

Answer 1

What will happen if you have more than two rows for same ID and Type? The query should return all the values except with Grade Z. Use below query to achieve that.

I have added an extra column in your query named "rownumber" to numbered each row according to ID and Type with order by Grade. That means each row with same ID and Type will have a sequence number starting from 1. This sequence will be maintained in ascending order of grade. Then I have used Common Table Expression and in where clause I have chosen first row for each id and type (having rownumber 1) along with all the rows with rownumber >1 and grade<>'z' to eliminate any rows with grade "Z" for which IT and Type at least one row already exists.

    create table t1 (ID          int ,AMOUNT int)
    insert into t1 values(1             , 100)
    insert into t1 values(2              ,50)
    
    create table t2(ID          int ,Type varchar(10))
    insert into t2 values(1,              'A')
    insert into t2 values(2              ,'B')
    
    
    
    Create table t3 ([Type] varchar(10), grade varchar(10))
    insert into t3 values('A',              'X')
    insert into t3 values('B',              'Y')
    insert into t3 values('B',              'Z')
    insert into t3 values('B',              'X')
    
    
with cte as(
SELECT 
t1.ID as ID, 
t1.AMOUNT as AMOUNT, 
t2.TYPE as TYPE,
t3.GRADE as GRADE,
row_number()over (partition by t1.id,t2.type  order by grade) rownumber
FROM t1 
INNER JOIN t2 ON t1.ID=t2.ID
INNER JOIN t3 ON t2.TYPE=t3.TYPE)
select id,AMOUNT,type,grade,rownumber from cte where rownumber=1 or (rownumber>1 and GRADE<>'Z')

Answer 2

SELECT 
t1.ID as ID, 
t1.AMOUNT as AMOUNT, 
t2.TYPE as TYPE,
MIN(t3.GRADE) as GRADE
FROM t1 
INNER JOIN t1 ON t1.ID=t2.ID
INNER JOIN t2 ON t2.TYPE=t3.TYPE
GROUP BY t1.ID,
t1.AMOUNT,
t2.TYPE

What this does is perform an aggregate function on the Grade column and then eliminates duplicates in the other columns.