ok, after embarrassedly realizing that I phrased my question terribly
(thanks Alexander), here is a simplification and rewording of it --
(a) First I find all the "other nodes" in edges where a given $node_id
is either the from_node_id or to_node_id.
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
)
(b) Now, for each of the "other nodes" find the count of edges where
the "other nodes" appear as either the from_node_id or to_node_id
foreach "other node"
SELECT Count(node_id) AS count_of_other_node_id
FROM (
SELECT to_node_id AS node_id FROM edge WHERE from_node_id = $other_node_id
UNION
SELECT from_node_id AS node_id FROM edge WHERE to_node_id = $other_node_id
)
Is it possible to refine/combine the above two sets of queries into one?
If I am unable to do this in SQLite directly, or if it is too
expensive, I am considering perhaps to extract the entire edge set
for a given node, and then use either
<http://search.cpan.org/~jhi/Graph-0.84/lib/Graph.pod> or
<http://search.cpan.org/~tels/Graph-Easy-0.60/lib/Graph/Easy.pm> to
the manipulations.
On 2/9/08, P Kishor <[EMAIL PROTECTED]> wrote:
> 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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
