Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Left Join doesn't return all recodrs in left table

Author  Topic 

HelalM
Starting Member

19 Posts

Posted - 2012-11-27 : 22:54:50
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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-11-28 : 00:14:50
is that the actual query that you used ?
cause, i doubt you can run that query. You missed the alias on the ID

select t1.ID, Name
from tab1 t1
left outer join tab2 t2 on t1.id=t2.id




KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

HelalM
Starting Member

19 Posts

Posted - 2012-11-28 : 02:07:08
This is not the actual query. But it conveys the point. Anything else I should consider besides.

Thanks,
Helal

HM
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-11-28 : 02:51:04
please post the full query as you might some other condition in the WHERE clause that turns your LEFT JOIN to INNER JOIN


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

kkcmania
Starting Member

1 Post

Posted - 2012-11-28 : 05:44:46
Are you sure that you haven't specified any condition in where clause such as


select t1.id,t1.Name
from tab1 t1
left outer join tab2 t2 on t1.id=t2.id
where not t2.id is null.

Bcoz, as per your description, this query doesn't produce the result that u said. unless it is specified in the form as i said.
Go to Top of Page

HelalM
Starting Member

19 Posts

Posted - 2012-11-28 : 19:46:28
You got me go back and double check the wehere clause and there it was the culprit.....

Thax for the help

HM
Go to Top of Page
   

- Advertisement -