Question: SQL Select Technique

From: Michael B. Klein <michael_at_kbk.org>
Date: 1997/01/18
Message-ID: <01bc0592$6d7acde0$111998ce@trooper>#1/1

I'm writing a web application that accesses an Oracle database, and I have an SQL question. My data is structured in the following way:

A-->>B
B-->>B or C
C-->>D or E
D-->>E

A-->>F
B-->>F
C-->>F

D-->>F
E-->>F

(all relationships are one-to-many)

My problem is with the fact that B can "drill down" to other B's before falling through to C, and that C can relate either directly to E or go through D first. Also, F (which is a footnote table), can be referenced by any of the other tables.

I come from a desktop database background (Access, Paradox, dBase, whatever) where the solution would be to join the tables, set filters, then find everything else by seeking and browsing. Of course, that's exactly what you CAN'T do with SQL. Of course, I could always SELECT my A's and B's, then step through the cursor and do a new, separate SELECT for each B, stepping through that cursor to do my C's, etc. That seems kind of time consuming and wasteful, however. Is there a realistic way to get all my data in one big complex SELECT statement, even considering the fact that I don't know how many levels of B I have to go through before I get to C?

Thanks,
Michael Received on Sat Jan 18 1997 - 00:00:00 CST