Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Three way JOIN with occasional nulls

Three way JOIN with occasional nulls

From: Graeme St. Clair <Graeme.St.Clair_at_hds.com>
Date: Thu, 17 Mar 2005 18:06:02 -0800
Message-ID: <5E927FAE154F7A4FB83E49E1139F137F06AAE8ED@ussccem04.hds.com>


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
Received on Thu Mar 17 2005 - 21:09:38 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US