RE: query problem (left outer join)

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Mon, 2 Oct 2006 11:56:35 -0700
Message-ID: <22EC50E60931494FA666A8CF8260C45B5D9AA6@ALVMBXW05.prod.quest.corp>

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] 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_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

--

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