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

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                                          
Paul                                 avocado                                          
Frank      apple                                                
Frank      Cherry
0
arnololo123
Asked:
arnololo123
  • 3
  • 2
1 Solution
 
SharathData 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

Open in new window


Tested with your sample data.
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)
insert @Tpersonveggie values (1,'lettuce',1),(2,'Spinash',2),(3,'avocado',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
Paul	NULL	avocado
Frank	NULL	NULL
*/

Open in new window

0
 
pcelbaCommented:
To obtain the result desired is impossible because there is no connection between Frank and Cherry as visible in the previous answer.
0
 
arnololo123Author Commented:
sorry I forget to add in the Table TfruitPerson   Franck with Cherry and apple.
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
arnololo123Author 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
 
SharathData 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)
insert @Tpersonveggie values (1,'lettuce',1),(2,'Spinash',2),(3,'avocado',2),(4,'spinach',1),(5,'veg',1)

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
2	Paul	2	NULL	avocado
3	Frank	NULL	NULL	NULL
*/

Open in new window

1
 
arnololo123Author 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.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now