| Left Outer Join in Oracle [message #227740] |
Thu, 29 March 2007 04:29  |
sandhyaa
Messages: 79
Registered: February 2007
|
Member |
|
|
Hi All,
How do we write a left outer join in Oracle in the below case using (+)
I have written this query in SQL Server and it works fine. How do we rewrite this in Oracle way. I know INNER JOIN/LEFT OUTER JOIN works in 9i onwards, but interested to know how the query would look like in its traditional syntax.
Query:
SELECT tab2.EMPLOYEE_ID, tab1.TRANS_DATE, tab1.Time, tab1.JOB_ID, tab3.charge
FROM
table1 tab1
inner join table2 tab2
ON tab2.TRANS_ID=tab1.TRANS_ID
LEFT OUTER JOIN table3 tab3
ON tab2.EMPLOYEE_ID=tab3.EMPLOYEE AND tab1.TRANS_DATE
BETWEEN tab3.START_DATE_ACTIVE AND NVL(tab3.END_DATE_ACTIVE, '31-MAR-4712')
AND tab1.JOB_ID=tab3.JOB_ID
Thanks in advance
Sandi
[EDITED by DJM: cut overly long line]
[Updated on: Wed, 09 June 2010 19:17] by Moderator Report message to a moderator |
|
|
|
| Re: Left Outer Join in Oracle [message #227745 is a reply to message #227740] |
Thu, 29 March 2007 04:48  |
asherisfine
Messages: 63
Registered: June 2006
Location: India
|
Member |
|
|
Hi sandhya
If You have 2 tables say employees and dept that you want to do a left outer join on then this is how you do it.
select a.ename,b.dname,a.sal from emp a,dept b where
b.deptno=a.deptno(+)
ENAME DNAME SAL
----------------------------
CLARK ACCOUNTING 2450
KING ACCOUNTING 5000
MILLER ACCOUNTING 1300
SMITH RESEARCH 800
ADAMS RESEARCH 1100
FORD RESEARCH 3000
SCOTT RESEARCH 3000
JONES RESEARCH 2975
ALLEN SALES 1600
BLAKE SALES 2850
MARTIN SALES 1250
JAMES SALES 950
TURNER SALES 1500
WARD SALES 1250
OPERATIONS
Here i did a left outer join between dept table and emp table
and it returns me records from dept table for which there are no values in emp table.
An easy way to remember this would be
if you want to do
a left outer join b
on a.id=b.id
put it as
where a.id=b.id(+)
ie. if you want to do left outer join the (+) comes at the end of condition on RIGHT hand side.
if you wanted to do
a right outer join b
on a.id=b.id
put it as
where a.id(+)=b.id
Here we see that the (+) comes at the LEFT of equality operator
|
|
|
|
| Re: Left Outer Join in Oracle [message #227747 is a reply to message #227745] |
Thu, 29 March 2007 04:54 |
sandhyaa
Messages: 79
Registered: February 2007
|
Member |
|
|
Thanks for the update.
Can you please helpe in rewriting the same for the query in my example.
When I try to write it gives error: "a table may be outer joined to at most one other table"
Regards,
Sandi
|
|
|
|
| Re: Left Outer Join in Oracle [message #227749 is a reply to message #227740] |
Thu, 29 March 2007 04:59 |
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
|
Senior Member |
|
|
You might be able to restructure it using inline views to avoid the multiple outer joins. It would look something like:
SELECT tab23.EMPLOYEE_ID, tab1.TRANS_DATE, tab1.JOB_ID, tab23.start_date_active
FROM
(
select tab2.EMPLOYEE_ID, tab3.start_date_active, tab3.job_id, tab2.trans_id, tab3.end_date_active
from
tab2 tab2,
tab3 tab3
where tab2.EMPLOYEE_ID=tab3.EMPLOYEE (+)
) tab23,
tab1 tab1
where tab1.TRANS_DATE BETWEEN tab23.START_DATE_ACTIVE (+) AND NVL(tab23.END_DATE_ACTIVE(+), '31-MAR-4712')
and tab23.TRANS_ID(+)=tab1.TRANS_ID
AND tab1.JOB_ID=tab23.JOB_ID (+)
|
|
|
|
|
|
| Re: Left Outer Join in Oracle [message #227760 is a reply to message #227747] |
Thu, 29 March 2007 05:31 |
asherisfine
Messages: 63
Registered: June 2006
Location: India
|
Member |
|
|
Hi sandhya
Oracle is reporting this error because you are using the table tab2 multiple time for join .
select tab_newtab2.EMPLOYEE_ID,tab_newtab2.TRANS_DATE ,tab_newtab2.Time ,tab_newtab2.JOB_ID
from
(SELECT tab2.EMPLOYEE_ID as EMPLOYEE_ID, tab1.TRANS_DATE as TRANS_DATE , tab1.Time as Time ,
tab1.JOB_ID as JOB_ID,tab1.TRANS_DATE as TRANS_DATE
FROM
table1 tab1, tab2
where tab2.TRANS_ID=tab1.TRANS_ID) as tab_newtab2,tab3
where tab_newtab2.EMPLOYEE_ID=tab3.EMPLOYEE
and tab_newtab2.TRANS_DATE BETWEEN tab3.START_DATE_ACTIVE AND NVL(tab3.END_DATE_ACTIVE, '31-MAR-4712')
and tab_newtab2.JOB_ID=tab3.JOB_ID
As i understand your requirement this is the query.
"Cthulhu" mate I haven't understood the query you wrote.
Please let me know if iam wrong
Thanks and regards
Asher
[EDITED by DJM: cut overly long line]
[Updated on: Wed, 09 June 2010 19:16] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: Left Outer Join in Oracle [message #227765 is a reply to message #227749] |
Thu, 29 March 2007 06:05 |
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
|
Senior Member |
|
|
SELECT tab2.employee_id
, tab1.trans_date
, tab1.TIME
, tab1.job_id
, tab3.charge
FROM table1 tab1
, table2 tab2
, table3 tab3
WHERE tab2.trans_id = tab1.trans_id
AND tab2.employee_id = tab3.employee(+)
AND tab1.trans_date BETWEEN tab3.start_date_active(+) AND NVL
(tab3.end_date_active(+)
, '31-MAR-4712'
)
AND tab1.job_id = tab3.job_id(+)[Updated on: Thu, 29 March 2007 06:48] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
