Re: Question: SQL Select Technique

In article <01bc0592$6d7acde0$111998ce_at_trooper>, "Michael B. Klein" <michael_at_kbk.org> wrote:

>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?
>

As you say in the second last paragraph, there are two problems here: B relates to itself, and any table can relate to F.

The second problem you can probably solve with outer joins or a query that's a union of several selects. You do something like this pseudo-SQL:

select rows from F that match A
union select rows from F that match B
etc.

The first problem is hard. I think the theoretical explanation for why it's hard is that relations are sets (in the mathematical sense), and the elements of a set have no order. When a tuple from B can relate to a tuple from B which can relate to a tuple from B, etc, you have order.

Try getting a good math text on set theory that includes ordered sets. See how they build the theory of ordered sets on basic set theory. That might give some insights on how to write an SQL query that suits your needs.

Sorry I can't give a more definite answer, but I'd need a more specific problem statement. If you want to pursue the discussion, e-mail me, since I don't check the newsgroup that often.

-- 
Larry Reid                                          lcreid_at_web.net