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_at_softex.lv] Sent: Friday, March 18, 2005 4:07 AM
To: Graeme.St.Clair_at_hds.com
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_at_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_at_CMISMGR2> create table c (id number not null, cname varchar2(10) not null);

Table created.

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

1 row created.

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

1 row created.

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

1 row created.

Elapsed: 00:00:00.00
10:46:05 gints_at_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_at_freelists.org

[mailto:oracle-l-bounce_at_freelists.org]
> On Behalf Of Graeme St. Clair
> Sent: Friday, March 18, 2005 4:06 AM
> To: 'Oracle-L_at_FreeLists.org'
> 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