I have a table of nodes and edges like so
CREATE TABLE edge (
edge_id INTEGER PRIMARY KEY,
from_node_id TEXT,
to_node_id TEXT,
..
);
CREATE TABLE node (
node_id INTEGER PRIMARY KEY,
node_name TEXT,
..
);
Given a $node_id, I want to find (a) all the edges where that node_id
appears either as a from_node_id or a to_node_id, and (b) a count of
the forward links as well. For (a), I do the following
SELECT node_id, node_name
FROM (
SELECT e.to_node_id AS node_id, n.node_name AS node_name
FROM edge e JOIN node n ON e.to_node_id = n.node_id
WHERE e.from_node_id = $node_id
UNION
SELECT e.from_node_id AS node_id, n.node_name AS node_name
FROM edge e JOIN node n ON e.from_node_id = n.node_id
WHERE e.to_node_id = $node_id
)
For (b), I can't think of any better way than looping over the result
of (a), and running the following query for each node_id in the result
(in this case, each node_id will be the forward looking node for the
original node_id). Psuedo-code ahead
foreach node_id AS $other_node_id in result-of-a
SELECT Count(node_id) AS count_of_other_node_id
FROM (
SELECT e.to_node_id AS node_id, n.node_name AS node_name
FROM edge e JOIN node n ON e.to_node_id = n.node_id
WHERE e.from_node_id = $other_node_id
UNION
SELECT e.from_node_id AS node_id, n.node_name AS node_name
FROM edge e JOIN node n ON e.from_node_id = n.node_id
WHERE e.to_node_id = $other_node_id
)
My questions -- is there a way to do both (a) and (b) better, and is
it possible to do them all in one query?
--
Puneet Kishor http://punkish.eidesis.org/
Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
