| Author |
 Topic  |
|
|
HelalM
Starting Member
USA
19 Posts |
 Posted - 11/27/2012 : 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)
Singapore
16746 Posts |
Posted - 11/28/2012 : 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
Time is always against us
|
 |
|
|
HelalM
Starting Member
USA
19 Posts |
Posted - 11/28/2012 : 02:07:08
|
This is not the actual query. But it conveys the point. Anything else I should consider besides.
Thanks,
Helal
HM |
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
Posted - 11/28/2012 : 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
Time is always against us
|
|
|
|
kkcmania
Starting Member
India
1 Posts |
Posted - 11/28/2012 : 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. |
|
|
|
HelalM
Starting Member
USA
19 Posts |
Posted - 11/28/2012 : 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 |
|
|
| |
Topic |
|
Left Join doesn't return all recodrs in left table
