Query to find all the nodes that are two steps away from a particular node.

Question

Suppose I have two columns in a table that represents a graph, the first column is a FROMNODE and second one is TONODE. What I would like to know is that how will we find all the nodes that are two steps away from a particular node. Lets suppose I have a node numbered '1' and i would like to know all the nodes that are two steps away from it.

I have tried(I am assuming the table name as graph) SELECT FROMNODE FROM GRAPH WHERE TONODE=1 (this is to select all the nodes that are connected to node 1, but I couldn't figure out how would I find all the nodes that are two steps away from node 1??)

Answer 1

Take it one step at a time!

select step2.tonode
from   graph step1
join   graph step2 on step2.fromnode = step1.tonode
where  step1.fromnode = 1

Answer 2

Join back onto the same table...

SELECT g2.FromNode
FROM Graph as g1 
JOIN Graph as g2 ON g2.ToNode = g1.FromNode
WHERE g1.ToNode = @startnode
;