Hello Kishor, a: if you want to find all edges, why your query returns nodes?
So I think it should be like this: SELECT edge_id FROM edge WHERE from_node = $node OR to_node = $node; This query is not good, because "OR" drop out any index optimization. But As I see your example doesn't have indexes on from_node, to_node. So, if you want use all power of indexes, it'll be better to use: SELECT edge_id FROM edge WHERE from_node = $node UNION SELECT edge_id FROM edge WHERE to_node = $node If you don't use loop edges, it will be faster to use "UNION ALL". I am not really understand what is "count of the forward links". Can you give more detailed definition? 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
