Howdy, Stranger!

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

Sign In with Facebook Sign In with Google Sign In with OpenID

Categories

We have migrated to a new platform! Please note that you will need to reset your password to log in (your credentials are still in-tact though). Please contact lee@programmersheaven.com if you have questions.
Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

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.