470,580 Members | 2,208 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,580 developers. It's quick & easy.

Some SQL Help Please

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.
Jul 19 '05 #1
2 1467
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
Jul 19 '05 #2
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
Jul 19 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by mike | last post: by
193 posts views Thread by Michael B. | last post: by
3 posts views Thread by ashutosh | last post: by
4 posts views Thread by muddasirmunir | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.