Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

Remove duplicate from left join

Posted on 2016-09-06
Medium Priority
86 Views
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
Question by:arnololo123
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 3
• 2

LVL 41

Expert Comment

ID: 41786827
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

LVL 43

Expert Comment

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

Author Comment

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

Author Comment

ID: 41786862
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

LVL 41

Accepted Solution

Sharath earned 2000 total points
ID: 41786893
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 Closing Comment

ID: 41787061
awesome thank you
0

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.