Some SQL Help Please

[Charlie Bursell]

Using Oracle 8, so no ANSI joins

Given a DB structure some like a tree:
A
|
B -- C
|
D
|
F -- E
|
G -- H -- I

I issue a select like:
SELECT A.colx, B.colx, C.colx, D.colx, E.colx, F.colx,
G.colx, H.colx, I.colx

FROM A, B, C, D, E, F, G, H, I

WHERE A.key = B.key AND C.key = B.key AND D.key = C.key
AND E.key = D.key AND F.KEY = E.key AND H.key = E.key
AND G.key = H.key AND I.key = H.key;

How do I make this work if all tables do not contain data for all
records. For example, A, B, D, E, F, and H have data but other tables
(G and I) do not.

How do I get all data except for those tables (Leaving G.colx and
I.colx) null in the response?

Hope this makes sense.

[Hans Forbrich]

Re: Some SQL Help Please

Charlie Bursell wrote:

Using Oracle 8, so no ANSI joins
>

Just hints (perhaps others will be kind enough to work this with you)

a) Try writing it using ANSI joins and then look at the docco to map the
'ANSI' back to Oracle's outer join capability.

b) Ask yourself why you are still using Oracle8. At least for test
purposes, you could create an Oracle9i or Oracle10g harness and check it
out. If this is a one-off question, possibly use that harness with DB links
back to the Oracle8 database.


/Hans

[Jack]

Re: Some SQL Help Please

Hi Charlie;

You'll have to use the old fashioned outer join syntax.

This consists of adding the characters "(+)" onto the column(s) in
your WHERE clause which refer to the 'optional' tables. By 'optional'
I mean the tables that might or might not have matching rows.

This would change your WHERE clause from:

>
WHERE A.key = B.key AND C.key = B.key AND D.key = C.key
AND E.key = D.key AND F.KEY = E.key AND H.key = E.key
AND G.key = H.key AND I.key = H.key;
>

to:

>
WHERE A.key = B.key AND C.key = B.key AND D.key = C.key
AND E.key = D.key AND F.KEY = E.key AND H.key = E.key
AND G.key(+) = H.key AND I.key(+) = H.key;

Cheers, have fun tuning it!

Jack