Solved

Remove duplicate from left join

Posted on 2016-09-06
6
65 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 40

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 41

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 40

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

911 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now