Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Left Join doesn't return all recodrs in left table


Left Join doesn't return all recodrs in left table

Author
Message
helal.mobasher 13209
helal.mobasher 13209
Valued Member
Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)

Group: General Forum Members
Points: 58 Visits: 219
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
thava
thava
SSC Veteran
SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)

Group: General Forum Members
Points: 264 Visits: 556
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
subbu1
subbu1
SSC Eights!
SSC Eights! (947 reputation)SSC Eights! (947 reputation)SSC Eights! (947 reputation)SSC Eights! (947 reputation)SSC Eights! (947 reputation)SSC Eights! (947 reputation)SSC Eights! (947 reputation)SSC Eights! (947 reputation)

Group: General Forum Members
Points: 947 Visits: 695
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
rhythm.varshney
rhythm.varshney
SSC Journeyman
SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)

Group: General Forum Members
Points: 88 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.
richykong
richykong
SSC-Enthusiastic
SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)

Group: General Forum Members
Points: 134 Visits: 621
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'".
helal.mobasher 13209
helal.mobasher 13209
Valued Member
Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)

Group: General Forum Members
Points: 58 Visits: 219
Thank you so much for your quick response. With your tip, I found the problem in where clause. Thax again.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search