RE: query problem (left outer join)
- From: "Jacques Kilchoer" <Jacques.Kilchoer@xxxxxxxxx>
- To: <Harvinder.Singh@xxxxxxxxxxxxx>, "oracle-l" <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 2 Oct 2006 11:56:35 -0700
Maybe I don't understand the query, but is this the results you are expecting
(this query assumes that TAB3.NM is a NOT NULL column) If my query isn't right,
perhaps you could show what results you would expect from the test data and
that would help people smarter than me write the query you need.
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, tab2 av, tab3 ed
where
accmap.id_acc in (4, 345)
and accmap.id_acc = av.id_acc (+)
and av.c_contacttype = ed.id (+)
and (ed.nm is null or ed.nm = 'bill-to') ;
Here is my test data and the query results:
create table tab1 (id_acc number) ;
create table tab2 (id_acc number, c_contacttype number,
c_firstname varchar2 (10), c_lastname varchar2 (10),
c_middleinitial varchar2 (1), c_zip varchar2 (5)) ;
create table tab3 (id number, nm varchar2 (7) not null) ;
insert into tab1 (id_acc) values (1) ;
insert into tab1 (id_acc) values (4) ;
insert into tab1 (id_acc) values (345) ;
insert into tab2
(id_acc, c_contacttype, c_firstname, c_lastname,
c_middleinitial, c_zip)
select 1, 1, 'HARRY', 'TRUMAN', 'S', '00100' from dual
union all
select 1, 2, 'GEORGE', 'WASHINGTON', 'F', '00101' from dual
union all
select 1, 3, 'FRANKLIN', 'ROOSEVELT', 'D', '00102' from dual
union all
select 3, 1, 'JAMES', 'BUCHANAN', 'A', '00400' from dual
union all
select 3, 2, 'ABRAHAM', 'LINCOLN', 'J', '00401' from dual
union all
select 3, 3, 'MILLARD', 'FILLMORE', null, '00402' from dual
union all
select 345, 1, 'QUINCY', 'ADAMS', 'X', '34500' from dual
union all
select 345, 2, 'HERBERT', 'HOOVER', 'J', '34501' from dual
union all
select 345, 3, 'ULYSSES', 'GRANT', 'S', '34502' from dual ;
insert into tab3 (id, nm) values (1, 'abc') ;
insert into tab3 (id, nm) values (2, 'bill-to') ;
commit ;
SQL> SELECT
2 accmap.id_acc as id_acc,
3 av.c_firstname as firstname,
4 av.c_lastname as lastname,
5 av.c_middleinitial as middleinitial,
6 av.c_zip as zip
7 FROM
8 Tab1 accmap, tab2 av, tab3 ed
9 where
10 accmap.id_acc in (4, 345)
11 and accmap.id_acc = av.id_acc (+)
12 and av.c_contacttype = ed.id (+)
13 and (ed.nm is null or ed.nm = 'bill-to') ;
ID_ACC FIRSTNAME LASTNAME M ZIP
--------- ---------- ---------- - -----
345 HERBERT HOOVER J 34501
345 ULYSSES GRANT S 34502
4
-----Message d'origine-----
De : oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] De la
part de Harvinder Singh
Envoyé : lundi, 2. octobre 2006 10:15
À : Nigel Thomas; oracle-l
Objet : RE: query problem (left outer join)
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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
