Simple Graph Search Algorithm in SQL (PostgreSQL)
Something like this:
with recursive graph_cte (node1, node2, start_id) as( select node1, node2, id as start_id from graphs where node1 = 1 -- alternatively elect the starting element using where id = xyz union all select nxt.node1, nxt.node2, prv.start_id from graphs nxt join graph_cte prv on nxt.node1 = prv.node2)select start_id, node1, node2from graph_cteorder by start_id;(requires PostgreSQL 8.4 or higher)
SQL is not best suited to manipulating graphs and finding paths. You're probably better off loading the graph in to a procedural language and using the Floyd-Warshall algorithm or Johnson's algorithm to find a path between nodes.
However, if you really must use SQL then I suggest you pick up a copy of Joe Celko's SQL for Smarties which has an entire chapter devoted to graphs in SQL.
You can use graph database directly to solve your problem:https://launchpad.net/oqgraph -> graph as mysql storage engine