Re: SQL help requested - "linked list"

From: Mia <nospam_at_cox.net>
Date: Thu, 21 Aug 2003 16:50:02 -0700
Message-ID: <3F455AAA.5010103@cox.net>

Lennart Jonsson wrote:
> 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?
>>
>>-Mia

>
>
> What database are you using? In Oracle and DB2 you can express
> recursion. I'm only familiar with DB2, so here's one variant:
>
>
> create table list (
> id int not null primary key,
> nextid int
> );
>
> -- chain 1
> insert into list (id, nextid) values
> (1,2), (2,3), (3,9), (9,14);
> insert into list (id) values (14);
>
> -- chain 2
> insert into list (id, nextid) values
> (6,12), (12,13), (13,19), (19,24);
> insert into list (id) values (24);
>
> -- retrive chain for id = 3
>
> with before_chain (id, seq) as (
> values (3,0)
> union all
> select l.id,seq-1 from before_chain c, list l where l.nextid =
> c.id
> ), after_chain (id, seq) as (
> values (3,0)
> union all
> select nextid,seq+1 from after_chain c, list l where l.id = c.id
> ) select * from before_chain
> union
> select * from after_chain where id is not null
> order by 2
>
> ID SEQ
> ----------- -----------
> SQL0347W The recursive common table expression "JON.AFTER_CHAIN" may
> contain
> an infinite loop. SQLSTATE=01605
>
> 1 -2
> 2 -1
> 3 0
> 9 1
> 14 2
>
> 5 record(s) selected with 1 warning messages printed.
>
>
>
> HTH
> /Lennart

Thanks Lennart!

Thats seems to do what I want. Now, if I could just figure out HOW. Can someone help out with an Oracle equivalent?

-Mia Received on Thu Aug 21 2003 - 18:50:02 CDT