Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> 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-lReceived on Thu Mar 17 2005 - 21:09:38 CST