To: "Nigel Thomas" <nigel@xxxxxxxxxxxxxx>, "oracle-l" <oracle-l@xxxxxxxxxxxxx>
Date: Mon, 2 Oct 2006 13:14:56 -0400
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@xxxxxxxxxxxxxx]
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
