A tricky Left-outer Join :- - Programmers Heaven

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

A tricky Left-outer Join :-

technocrat181technocrat181 Posts: 1Member
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 !
Sign In or Register to comment.