Solved

Remove duplicate from left join

Posted on 2016-09-06
6
53 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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

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

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

746 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

13 Experts available now in Live!

Get 1:1 Help Now