SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Left Join doesn't return all recodrs in left table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

HelalM
Starting Member

USA
19 Posts

Posted - 11/27/2012 :  22:54:50  Show Profile  Reply with Quote
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)

Singapore
17601 Posts

Posted - 11/28/2012 :  00:14:50  Show Profile  Reply with Quote
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
Time is always against us

Go to Top of Page

HelalM
Starting Member

USA
19 Posts

Posted - 11/28/2012 :  02:07:08  Show Profile  Reply with Quote
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)

Singapore
17601 Posts

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


KH
Time is always against us

Go to Top of Page

kkcmania
Starting Member

India
1 Posts

Posted - 11/28/2012 :  05:44:46  Show Profile  Reply with Quote
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

USA
19 Posts

Posted - 11/28/2012 :  19:46:28  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000