Re: SQL question

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1998/09/22
Message-ID: <3607e39a.103613748_at_192.86.155.100>#1/1


A copy of this was sent to kshave_at_mbnet.mb.ca (Keith S.) (if that email address didn't require changing) On Tue, 22 Sep 1998 16:50:32 GMT, you wrote:

> I have 3 tables I want to query.
>
>TABLE A
> col m
> col n
>
>TABLE B
> col m
> col x
>
>TABLE C
> col x
> col y
>
> I wish to select a.m,b.x,c.y
> from a,b,c
> where a.m = b.m
> and b.x = c.x
>
> Now, I wanted to outer join tables A and B because I still want to include
>the a.m values for table A records with no corresponding table B records.
>However, these table A records were not showing up because of the link of
>table B to table C. Any suggestions?

outer join from B to C as well but removing any records that were 'created' as a result of this. For example:

create table a ( m int, n int );
create table b ( m int, x int );
create table c ( x int, y int );

insert into a values ( 1, 1 );

insert into a values ( 2, 2 );
insert into a values ( 3, 3 );
insert into b values ( 2, 2 );
insert into b values ( 3, 3 );
insert into b values ( 4, 4 );

insert into c values ( 3, 3 );

insert into c values ( 4, 4 );
insert into c values ( 5, 5 );

select a.m, b.x, c.y
  from a, b, c

 where a.m = b.m (+)
   and b.x = c.x (+)
   and NOT ( b.x is not null and c.x is null )
/

         M X Y
---------- ---------- ----------

         3          3          3
         1


We get row 3, 3, 3 since all three tables were involved OK. We get rows 1, null, null since A had 1,1 but B did not have a mate (and you said you wanted to keep that row).

We did not get 2, 2, 2 since A had 2 and B had 2 but C did not (and we filtered that one out with the AND NOT () clause). You might choose to keep that one as well I guess...  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

--
http://govt.us.oracle.com/    -- downloadable utilities
 
----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation
 
Anti-Anti Spam Msg: if you want an answer emailed to you, 
you have to make it easy to get email to you.  Any bounced
email will be treated the same way i treat SPAM-- I delete it.
Received on Tue Sep 22 1998 - 00:00:00 CEST

Original text of this message