For your pseudo-code for (b) i can suggest this, but i still not
understand why you need it :-/
SUBQUERY =
SELECT e.to_node_id AS node_id FROM edge e WHERE
e.from_node_id = $node_id
UNION
SELECT e.from_node_id AS node_id FROM edge e WHERE
e.to_node_id = $node_id
SELECT count(edge_id)
FROM edge
WHERE
edge.to_node IN (SUBQUERY)
OR
edge.from_node IN (SUBQUERY)
I moved some SQL inside SUBQUERY for easy reading-understanding.
You can eliminate "OR" like in my previous email, but full query
will be really huge and unreadable.
> On Feb 9, 2008 4:10 PM, 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
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> Alexander Batyrshin aka bash
> bash = Biomechanica Artificial Sabotage Humanoid
>
--
Alexander Batyrshin aka bash
bash = Biomechanica Artificial Sabotage Humanoid
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
