Re: recursive queries in SQL

In article <3BF3DA59.6070408_at_backup.nl>, "Michiel van der Kraats" <mkraats_at_backup.nl> wrote:

> table node(node_id,name)
> table links(node_id1,node_id2)

> But this (of course) returns all connected nodes. What are some common
> ways of querying this type of data?
>
> In case it matters, I'm currently using Sybase SQL Anywhere 6.0.
>
>

In this dialect of SQL (Transact SQL, IIRC), there's no "pure SQL" way to query the data. But it's not that hard to write the procedural code. This loop will search the links from one node outward in the forward direction. If you want to go both ways (traverse links in the node2-node1 direction as well) add another insert in the loop which will go the opposite direction.

create table #connected( node_id int )

insert into #connected( node_id ) values ( _at_start_node_id )

while ( _at__at_rowcount > 0 )
 insert into #connected(node_id)
 select distinct l.node_id2
 from #connected c, links l
 where c.node_id = l.node_id1
 and l.node_id1 not in (select node_id from #connected )

select * from #connected -- all connected nodes

See earlier posts in the group on "tree" and "hierarchy" for other code/comments. Received on Thu Nov 15 2001 - 20:34:39 CET