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?
>
> -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 Received on Thu Aug 21 2003 - 17:41:06 CDT