RE: Three way JOIN with occasional nulls

Thanks to Guang Mei, Gints Plivna and Lex de Haan for replies and useful
vocabulary ("outer join"!).

The correct place for the (+) turned out to be as given below, and worked
like a charm.

Rgds, GStC.


-----Original Message-----
From: Gints Plivna [mailto:Gints.Plivna@xxxxxxxxx] 
Sent: Friday, March 18, 2005 4:07 AM
To: Graeme.St.Clair@xxxxxxx
Subject: RE: Three way JOIN with occasional nulls

It is called outer join. More info:
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/quer
ies7.htm#2054014


10:44:17 gints@CMISMGR2> create table a (id number not null, name
varchar2(10) not null, c_id number );

Table created.

Elapsed: 00:00:00.00
10:44:32 gints@CMISMGR2> create table c (id number not null, cname
varchar2(10) not null);

Table created.

Elapsed: 00:00:00.00
10:45:10 gints@CMISMGR2> insert into a values (1, 'cexists', 1);

1 row created.

Elapsed: 00:00:00.00
10:45:32 gints@CMISMGR2> insert into a values (2, 'cnotexists', 2);

1 row created.

Elapsed: 00:00:00.00
10:45:51 gints@CMISMGR2> insert into c values (1, 'cname');

1 row created.

Elapsed: 00:00:00.00
10:46:05 gints@CMISMGR2> select * from a, c 
10:46:15   2  where a.c_id = c.id (+);

             ID NAME                  C_ID              ID CNAME
--------------- ---------- --------------- --------------- ----------
              1 cexists                  1               1 cname
              2 cnotexists               2

2 rows selected.

Elapsed: 00:00:00.00

> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]
> On Behalf Of Graeme St. Clair
> Sent: Friday, March 18, 2005 4:06 AM
> To: 'Oracle-L@xxxxxxxxxxxxx'
> Subject: Three way JOIN with occasional nulls
> 
> As will become very obvious, I am not (yet?) an SQL person.  Oracle
8.1.7
> on
> Solaris, being interrogated via Perl + DBI.
> 
> I have a query that started as:-
> 
>  select a.manycols, b.bid, b.bvalue from a, b where a.aid = b.bid <and 
> other
> where-ness>
> 
> And it worked very well.  We added a 3rd d-b c, for this:-
> 
>  select a.manycols, b.bid, b.bvalue, c.cid, c.cvalue from a, b, c
where
> a.aid = b.bid and a.cid = c.cid <and other where-ness>
> 
> Unfortunately, it turns out that although a.bid can never be null,
a.cid
> can
> be, and of course when it is, I don't get these rows from a, tho I'm 
> really more interested in the <whereness> than the c.cvalue.
> 
> Can this query be modified to pick up rows from a even when a.cid is
null?
> (I'm perfectly happy to leave c.cvalue null, or '-', or 'unknown' or
> whatever.)  The essential thing is to see all rows that match
<whereness>,
> and c.cvalue is just "nice to have".
> 
> Rgds, GStC.
> 
> 
> 
> --
> http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l

Other related posts: