| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Three way JOIN with occasional nulls
with which rows of c do you want to join, in case a.cid contains a null value?
does the corresponding c.cid column contain null values as well?
after all, in the SELECT clause you have c.cid and c.cvalue ...
so Oracle has to display something :-)
in 8i, you should think in terms of using NVL functions to make a predicate
evaluate to true, for example:
... and nvl(a.cid,42) = nvl(c.cid,42) ...
or maybe you want an outer join? check out the Oracle SQL Reference for purpose and syntax...
kind regards,
Lex.
-----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 03:06
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
Received on Fri Mar 18 2005 - 04:38:02 CST
 |
 |