RE: query problem (left outer join)

From: Harvinder Singh <Harvinder.Singh_at_MetraTech.com>
Date: Mon, 2 Oct 2006 13:14:56 -0400
Message-ID: <D6424CD4C8A3C044BBC49877ED51C51801DBB0FB@ex2003.metratech.com>

This will change the semantic of the query, we need to get all records of accmap and then where value of tab2 does not matches left outer join will insert nulls, but if I use tab3 as inner join then that will remove still return data from tab2

-----Original Message-----

From: Nigel Thomas [mailto:nigel_at_preferisco.com] Sent: Monday, October 02, 2006 11:05 AM
To: Harvinder Singh; oracle-l
Subject: Re: query problem (left outer join)

Harvinder  

Include your tab3 BEFORE the outer join. Then no need for a subquery, no need to resolve a forward reference:

SELECT
    accmap.id_acc as id_acc,

    av.c_firstname as firstname, 
    av.c_lastname as lastname, 
    av.c_middleinitial as middleinitial, 
    av.c_zip as zip 

FROM
    Tab1 accmap
    INNER JOIN tab3 ed ON ed.nm = 'bill-to' LEFT OUTER JOIN tab2 av ON av.id_acc = accmap.id_acc AND av.c_contacttype = ED.ID
WHERE accmap.id_acc in (345)  

Regards Nigel
--

http://www.freelists.org/webpage/oracle-l Received on Mon Oct 02 2006 - 12:14:56 CDT