• Status: Solved
• Priority: Medium
• Security: Public
• Views: 95

# Remove duplicate from left join

HEllo,

I am doing two left join to a main table and I am alway getting duplicate.
I have tried many  different query buy I always get duplicates, I have simplify what I want below

Tperson                                   TFruitPerson                                          Tpersonveggie
idperson        Name            idFruitPerson   Fruit      idperson                  idVeggiePerson      Veggie      idperson
1                      John            1                         Apple              1                      1                            lettuce       1
2                      Paul            2                         coconut      1                          2                               Spinash       2
3                      Frank            3                         cherry              2                    3                               avocado      2

Result desired
Name      Fruit          Veggie
John      Apple          Lettuce
John      Coconut
Paul        Cherry          Spinash
Frank      apple
Frank      Cherry
0
arnololo123
• 3
• 2
1 Solution

Data EngineerCommented:
In your example, I don't find Frank having any fruits or veggies.
You can try like this.
``````select name,
case when rn1 >= rn2 then Fruit end as Fruit,
case when rn2 >= rn1 then Veggie end as Veggie
from Tperson p
left join (select *,row_number() over (partition by idperson order by idFruitPerson) rn1 from TFruitPerson) f on p.idperson = f.idperson
left join (select * , row_number() over (partition by idperson order by idVeggiePerson) rn2 from Tpersonveggie) v on p.idperson = v.idperson
``````

``````declare @Tperson table(idperson int, name varchar(10))
declare @TFruitPerson table(idFruitPerson int, Fruit varchar(10),idperson int)
declare @Tpersonveggie table(idVeggiePerson int, Veggie varchar(10),idperson int)

insert @Tperson values (1,'John'),(2,'Paul'),(3,'Frank')
insert @TFruitPerson values (1,'Apple',1),(2,'coconut',1),(3,'cherry',2)

select name,
case when rn1 >= rn2 then Fruit end as Fruit,
case when rn2 >= rn1 then Veggie end as Veggie
from @Tperson p
left join (select *,row_number() over (partition by idperson order by idFruitPerson) rn1 from @TFruitPerson) f on p.idperson = f.idperson
left join (select * , row_number() over (partition by idperson order by idVeggiePerson) rn2 from @Tpersonveggie) v on p.idperson = v.idperson

/*
name	Fruit	Veggie
John	Apple	lettuce
John	coconut	NULL
Paul	cherry	Spinash
Frank	NULL	NULL
*/
``````
0

Commented:
To obtain the result desired is impossible because there is no connection between Frank and Cherry as visible in the previous answer.
0

Author Commented:
sorry I forget to add in the Table TfruitPerson   Franck with Cherry and apple.
0

Author Commented:
Sharath, thanks but it does not work properly if a person has 2 fruits AND 2 veggies, I am getting a result of 4 lines instead of 2.
0

Data EngineerCommented:
try this.
``````declare @Tperson table(idperson int, name varchar(10))
declare @TFruitPerson table(idFruitPerson int, Fruit varchar(10),idperson int)
declare @Tpersonveggie table(idVeggiePerson int, Veggie varchar(10),idperson int)

insert @Tperson values (1,'John'),(2,'Paul'),(3,'Frank')
insert @TFruitPerson values (1,'Apple',1),(2,'coconut',1),(3,'cherry',2)

select *
from @Tperson p
left join (
select coalesce(f.idperson,v.idperson) idperson, Fruit,Veggie
from (select *, row_number() over (partition by idperson order by idFruitPerson) rn1 from @TFruitPerson) f
full join (select *,row_number() over (partition by idperson order by idVeggiePerson) rn2 from @Tpersonveggie) v on f.idperson = v.idperson and rn1=rn2) fv
on p.idperson = fv.idperson
/*
idperson	name	idperson	Fruit	Veggie
1	John	1	Apple	lettuce
1	John	1	coconut	spinach
1	John	1	NULL	veg
2	Paul	2	cherry	Spinash
3	Frank	NULL	NULL	NULL
*/
``````
1

Author Commented:
awesome thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.