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_at_cox.net>
>
>
> 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
Date: Thu, 21 Aug 2003 16:50:02 -0700
Message-ID: <3F455AAA.5010103_at_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
-Mia Received on Fri Aug 22 2003 - 01:50:02 CEST