Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Left Join doesn't return all recodrs in left table Expand / Collapse
Author
Message
Posted Tuesday, November 27, 2012 8:58 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 3:04 PM
Points: 31, Visits: 134
I am linking two tables with left outer join. I need to keep all the records in the left table even though there are no matches in the right one. I thought that's the purpose of using left outer join. Here is the example: tab1 ID=ranges from 01 to 10 and tab2 ID ranges from 01 to 10 with missing IDs.
select ID, Name
from tab1 t1
left outer join tab2 t2 on t1.id=t2.id

results:
t1.id,t1.name
01,A
02,B
03,C
06,F
08,I

since t2.ids 4,5,7,9,,10 are missing, these records are excluded from the result set.
Any help will be greatly appreciated.

Thanks,

Helal
Post #1389491
Posted Tuesday, November 27, 2012 9:32 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, September 14, 2014 12:30 AM
Points: 253, Visits: 540
may i know what is the purpose of joining table2 there
and
seems you post only part of your query please post entire query to resolve your issue




Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script
Post #1389497
Posted Wednesday, November 28, 2012 12:04 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, December 8, 2014 7:35 AM
Points: 888, Visits: 672
please check below codes.....
declare @t1 table(id int,name varchar(10))
insert into @t1(id,name) values(1,'A'),(2,'B'),(3,'C'),(4,'D'),(5,'E'),(6,'F'),(7,'G'),(8,'H'),(9,'I'),(10,'J')
declare @t2 table(id int,name varchar(10))
insert into @t2(id,name) values(1,'A'),(2,'B'),(3,'C'),(6,'F'),(8,'H')
----------------------LEFT JOIN------------------
select T1.ID, T1.Name
from @t1 t1
left outer join @t2 t2 on t1.id=t2.id
-------------------RIGHT JOIN
select T1.ID, T1.Name
from @t1 t1
RIGHT outer join @t2 t2 on t1.id=t2.id
Post #1389523
Posted Wednesday, November 28, 2012 12:13 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, September 19, 2013 4:30 AM
Points: 78, Visits: 195
helal.mobasher 13209 (11/27/2012)
I am linking two tables with left outer join. I need to keep all the records in the left table even though there are no matches in the right one. I thought that's the purpose of using left outer join. Here is the example: tab1 ID=ranges from 01 to 10 and tab2 ID ranges from 01 to 10 with missing IDs.
select ID, Name
from tab1 t1
left outer join tab2 t2 on t1.id=t2.id

results:
t1.id,t1.name
01,A
02,B
03,C
06,F
08,I

since t2.ids 4,5,7,9,,10 are missing, these records are excluded from the result set.
Any help will be greatly appreciated.

Thanks,

Helal


posted query will not work becuase of ambiguity.
Post #1389528
Posted Wednesday, November 28, 2012 9:28 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, October 20, 2014 9:30 AM
Points: 132, Visits: 588
Are you using a where clause based on tab2?
Where clause is applied after the left outer join. This means that the left outer join will return all records from tab1 with non joining records in tab2 having null on all fields. When you put a where clause on a tab2 field, you have to account for the nulls.
When doing this type of join and filter, I usually put the all filter conditions for tab2 during the on clause. Something like "on t1.id = t2.id AND t2.name = 'A'".
Post #1389910
Posted Wednesday, November 28, 2012 5:43 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 3:04 PM
Points: 31, Visits: 134
Thank you so much for your quick response. With your tip, I found the problem in where clause. Thax again.
Post #1390214
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse