SQL Clone
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
SSC Veteran
SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)

Group: General Forum Members
Points: 288 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-Addicted
SSC-Addicted (487 reputation)SSC-Addicted (487 reputation)SSC-Addicted (487 reputation)SSC-Addicted (487 reputation)SSC-Addicted (487 reputation)SSC-Addicted (487 reputation)SSC-Addicted (487 reputation)SSC-Addicted (487 reputation)

Group: General Forum Members
Points: 487 Visits: 557
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
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1307 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 Veteran
SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)

Group: General Forum Members
Points: 232 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
Old Hand
Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)

Group: General Forum Members
Points: 354 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
SSC Veteran
SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)

Group: General Forum Members
Points: 288 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