 <p>Based on your updated info we can convert the commented out join to this:</p> <pre><code>and ( ( PRMA_Purchase_Request_Type_ID != 145 AND TBL_Purchase_Request_Master.PRMA_Purchase_Request_No *= TBL_Parts_In_Txn_Master.PITM_Related_Request_No ) OR ( PRMA_Purchase_Request_Type_ID = 145 AND TBL_Purchase_Request_Details.PRDE_MR_No *= TBL_Parts_In_Txn_Master.PITM_Related_Request_No ) ) </code></pre> <p>This means we need a left outer join between </p> <ol> <li><p><code>TBL_Purchase_Request_Master</code> and <code>TBL_Parts_In_Txn_Master</code> </p></li> <li><p><code>TBL_Purchase_Request_Details</code> and <code>TBL_Parts_In_Txn_Master</code></p></li> </ol> <p>You have already defined the first one in your existing FROM clause. You just have to add the condition to it. It doesn't make sense to AND it so try an OR:</p> <p>So I <em>think</em> your FROM should be like this (but it is very difficult to work out without seeing the data model)</p> <pre><code>FROM TBL_Purchase_Request_Details Inner Join TBL_Purchase_Request_Master ON PRMA_Purchase_Request_No = PRDE_Purchase_Request_No Left Join TBL_Parts_In_Txn_Master M ON ( ( M.PRDE_Parts_Spec_ID = PITM_Parts_Spec_ID AND M.PRDE_Service_Center_ID = PITM_Service_Center_ID AND M.PRDE_Required_Delivery_Date = PITM_Expected_Arrival_Date ) OR ( TBL_Purchase_Request_Master.PRMA_Purchase_Request_Type_ID != 145 AND TBL_Purchase_Request_Master.PRMA_Purchase_Request_No = M.PITM_Related_Request_No ) OR ( TBL_Purchase_Request_Master.PRMA_Purchase_Request_Type_ID = 145 AND TBL_Purchase_Request_Details.PRDE_MR_No = M.PITM_Related_Request_No ) </code></pre> <p>I know it's annoying working with old code but it's worth keeping in mind that if it's a complex transformation, the code to transform it is going to be complicated. Would you rather have a bunch of different views pieced together (each of which you can unit test), or one HUMUNGOUS SQL statement representing all of those views in one go? Often stuff is built this way under tight constraints too. Managers are not interested in doing things properly, only coders are, but we don't pull the strings.</p>