recursive queries in SQL
From: Michiel van der Kraats <mkraats_at_backup.nl>
Date: Thu, 15 Nov 2001 16:08:09 +0100
Message-ID: <3BF3DA59.6070408_at_backup.nl>
Hi,
Date: Thu, 15 Nov 2001 16:08:09 +0100
Message-ID: <3BF3DA59.6070408_at_backup.nl>
Hi,
I'm playing with a (for now) simple model to describe network links/linked objects in an RDBMS (for example, item A connected to B, A connected to C, B connected to C). I'm using the following structure:
table node(node_id,name)
primary key node
table links(node_id1,node_id2) primary key node_id1, nodeid2 foreign key node_id1 references node foreign key node_id2 references node
This model seems to work, except I can't come up with a way to query the data, for example: for a given node, return all connected nodes. I came up with:
select * from nodes, links
where links.node_id1 = nodes.id or links.node_id2 = nodes.id
-- Michiel van der KraatsReceived on Thu Nov 15 2001 - 16:08:09 CET