?
Solved

Remove duplicate from left join

Posted on 2016-09-06
6
Medium Priority
?
94 Views
Last Modified: 2016-09-06
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
Comment
Question by:arnololo123
  • 3
  • 2
6 Comments
 
LVL 41

Expert Comment

by:Sharath
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

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
 
LVL 43

Expert Comment

by:pcelba
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

by:arnololo123
ID: 41786837
sorry I forget to add in the Table TfruitPerson   Franck with Cherry and apple.
0
IT Degree with Certifications Included

Aspire to become a network administrator, network security analyst, or computer and information systems manager? Make the most of your experience as an IT professional by earning your B.S. in Network Operations and Security.

 

Author Comment

by:arnololo123
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

by:
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)
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
 

Author Closing Comment

by:arnololo123
ID: 41787061
awesome thank you
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Question has a verified solution.

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

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question