RE: Three way JOIN with occasional nulls
- From: "Lex de Haan" <lex.de.haan@xxxxxxxxxxxxxx>
- To: <Graeme.St.Clair@xxxxxxx>, "'Oracle-L@xxxxxxxxxxxxx'" <Oracle-L@xxxxxxxxxxxxx>
- Date: Fri, 18 Mar 2005 10:34:29 +0100
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.
---------------------------------------------
Visit my website at http://www.naturaljoin.nl
---------------------------------------------
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Graeme St. Clair
Sent: Friday, March 18, 2005 03:06
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.
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
