Re: SQL help requested - "linked list"

Mia <nospam_at_cox.net> wrote in message news:<3F44E6F9.4090306_at_cox.net>...
> Hi Everyone,
>
> I have a query I'm trying to do, and with my limited SQL knowledge I
> don't know how to go about it.
>
> Here's a simplified description of my challenge. I have data in a table
> that forms sets you might describe as singly-linked lists. For example,
> the table contains columns ID and NEXTID, where NEXTID points to the ID
> of some other row. The linked list ends when NEXTID has a zero value.
>
> Given one of the ID values (they're unique), I want to select the IDs
> that occur before and after in that particular linked list. In a
> differnet language I would approach this using a recursive function. Is
> this possible in SQL?

As you mentioned using oracle, your query is standard application of "connect by" construct. Although documentation examples give the impression that "connect by" works for trees only, it actually applies to arbitrary directed acycle graphs.

In case if you need to perform more sophisticated queries (like full transitive closure, or hierarchical total), search google for "hierarchical query oracle connect by". Also, search asktom.oracle.com website about "advanced" connect by techniques. And, finally, if readers of this newsgroup would find your query challenging enough, you can even expect the solution posted here:-) Received on Fri Aug 22 2003 - 02:19:18 CEST