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 Go to next message
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 #420883 is a reply to message #420877] Thu, 03 September 2009 00:38 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
http://stanford.edu/dept/itss/docs/oracle/10g/server.101/b10759/queries006.htm
please read this for joins
From your message it is not clear what are you trying to do.

[Updated on: Thu, 03 September 2009 00:44]

Report message to a moderator

Re: A Tricky Left-Outer Join :- [message #420885 is a reply to message #420877] Thu, 03 September 2009 00:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
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 !
Re: A Tricky Left-Outer Join :- [message #420968 is a reply to message #420883] Thu, 03 September 2009 08:56 Go to previous message
technocrat181
Messages: 3
Registered: September 2009
Location: Singapore
Junior Member
Thanks for the Oracle URL Ayush.
Previous Topic: Help in Procedure
Next Topic: creating a gap in rec_nos
Goto Forum:
  


Current Time: Sun Dec 11 02:17:40 CST 2016

Total time taken to generate the page: 0.06877 seconds