Re: SQL question
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