Solved

Remove duplicate from left join

Posted on 2016-09-06
6
78 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
[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
  • Learn & ask questions
  • 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 42

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 500 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

763 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