Home » SQL & PL/SQL » SQL & PL/SQL » A Tricky Left-Outer Join :- (Oracle SQL)
A Tricky Left-Outer Join :- [message #420877] |
Wed, 02 September 2009 23:46  |
technocrat181
Messages: 3 Registered: September 2009 Location: Singapore
|
Junior Member |
|
|
Hello SQL Pros,
I'm relatively new to Oracle / SQL programming & I have this requirement wherein a Table (named "Dealsummary") have to be Left-Outer joined with a set of other tables (Eg : "Riskrun") based on certain set of conditions.
The issue is that; in addition to the field (in "Dealsummary" table) using which the left-outer join has to be done, there's a field from the other table which should also be put into the WHERE clause; which when done & the query executed; looks like i'm given an Inner join result rather than a Left-outer join result. The essential part of the existing query is given below :
SELECT A.TradeID, B.PV // PV can be NULL or NOT NULL
FROM DEALSUMMARY A, RISKRUN B
WHERE A.TradeID = B.TradeID(+)
AND B.RunID = (SELECT MAX(C.RunID) FROM RISKRUN C
WHERE C.TradeID = A.TradeID)
Note : The RISKRUN table contains the fields; RunID, TradeID & PV.
To clarify more, This table ;
-> contains Many rows with many RunIDs for a single TradeID (hence selecting the latest RunID from using a subquery in the above SQL).
-> need not possess any rows / RunIDs for certain TradeIDs found from DEALSUMMARY table. Even then, A.TradeID values should be displayed with NULLs in B.PV forming a Left-Outer Join.
Can any of you please help me out in fixing this up & produce a left-outer join from this query maitaining all the conditions ?
I believe this is relatively simple for the experienced SQL programmers here.
Thankyou very much in advance for the help !
|
|
|
|
Re: A Tricky Left-Outer Join :- [message #420885 is a reply to message #420877] |
Thu, 03 September 2009 00:45   |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Your problem is that when there is no matching row in RISKRUN MAX is null, so add a condition to handle this case (using SCOTT standard schema):
SCOTT> select d.deptno, d.dname, e.empno, e.ename, e.sal
2 from dept d, emp e
3 where d.deptno = e.deptno (+)
4 and e.sal = ( select max(sal) from emp e2
5 where e2.deptno = e.deptno )
6 order by 1
7 /
DEPTNO DNAME EMPNO ENAME SAL
---------- -------------- ---------- ---------- ----------
10 ACCOUNTING 7839 KING 5000
20 RESEARCH 7902 FORD 3000
20 RESEARCH 7788 SCOTT 3000
30 SALES 7698 BLAKE 2850
4 rows selected.
SCOTT> select d.deptno, d.dname, e.empno, e.ename, e.sal
2 from dept d, emp e
3 where d.deptno = e.deptno (+)
4 and ( e.sal = ( select max(sal) from emp e2
5 where e2.deptno = e.deptno )
6 or e.sal is null )
7 order by 1
8 /
DEPTNO DNAME EMPNO ENAME SAL
---------- -------------- ---------- ---------- ----------
10 ACCOUNTING 7839 KING 5000
20 RESEARCH 7788 SCOTT 3000
20 RESEARCH 7902 FORD 3000
30 SALES 7698 BLAKE 2850
40 OPERATIONS
5 rows selected.
There are many other ways to write this query, I give you 3:
SCOTT> select d.deptno, d.dname, e.empno, e.ename, e.sal
2 from dept d,
3 ( select deptno, empno, ename, sal
4 from emp e1
5 where sal = ( select max(sal) from emp e2
6 where e2.deptno = e1.deptno )
7 ) e
8 where d.deptno = e.deptno (+)
9 order by 1
10 /
DEPTNO DNAME EMPNO ENAME SAL
---------- -------------- ---------- ---------- ----------
10 ACCOUNTING 7839 KING 5000
20 RESEARCH 7788 SCOTT 3000
20 RESEARCH 7902 FORD 3000
30 SALES 7698 BLAKE 2850
40 OPERATIONS
5 rows selected.
SCOTT> select d.deptno, d.dname, e.empno, e.ename, e.sal
2 from dept d,
3 ( select deptno, empno, ename, sal
4 from emp
5 where (deptno,sal) in
6 ( select deptno, max(sal) from emp
7 group by deptno )
8 ) e
9 where d.deptno = e.deptno (+)
10 order by 1
11 /
DEPTNO DNAME EMPNO ENAME SAL
---------- -------------- ---------- ---------- ----------
10 ACCOUNTING 7839 KING 5000
20 RESEARCH 7788 SCOTT 3000
20 RESEARCH 7902 FORD 3000
30 SALES 7698 BLAKE 2850
40 OPERATIONS
5 rows selected.
SCOTT> select d.deptno, d.dname, e.empno, e.ename, e.sal
2 from dept d,
3 ( select deptno, empno, ename, sal
4 from ( select deptno, empno, ename, sal,
5 rank() over (partition by deptno order by sal desc) rk
6 from emp )
7 where rk = 1 ) e
8 where d.deptno = e.deptno (+)
9 order by 1
10 /
DEPTNO DNAME EMPNO ENAME SAL
---------- -------------- ---------- ---------- ----------
10 ACCOUNTING 7839 KING 5000
20 RESEARCH 7788 SCOTT 3000
20 RESEARCH 7902 FORD 3000
30 SALES 7698 BLAKE 2850
40 OPERATIONS
5 rows selected.
The most efficient one depends on your data, indexes...
Regards
Michel
[Updated on: Thu, 03 September 2009 00:45] Report message to a moderator
|
|
|
Re: A Tricky Left-Outer Join :- [message #420967 is a reply to message #420885] |
Thu, 03 September 2009 08:55   |
technocrat181
Messages: 3 Registered: September 2009 Location: Singapore
|
Junior Member |
|
|
Thanks a lot Michel..That was brilliant & very useful !
Especially, the second query which exactly is the ideal solution for this SQL. I couldnt think of using NULL check to get around the limitation. You've solved my problem easily..
Once again, Thanks a lot !
|
|
|
|
Goto Forum:
Current Time: Sat Feb 15 20:31:52 CST 2025
|