Left Join doesn't return all recodrs in left table
-
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
-
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 [/url] -
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
-
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.
-
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'".
-
Thank you so much for your quick response. With your tip, I found the problem in where clause. Thax again.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply