title: Directed graph SQL
body: <p>I have the following data set, which represents nodes in a directed graph.</p>
  
  <pre><code>CREATE TABLE nodes (NODE_FROM VARCHAR2(10),
                      NODE_TO VARCHAR2(10));
  
  INSERT INTO nodes VALUES('GT','TG');
  INSERT INTO nodes VALUES('GG','GC');
  INSERT INTO nodes VALUES('AT','TG');
  INSERT INTO nodes VALUES('TG','GC');
  INSERT INTO nodes VALUES('GC','CG');
  INSERT INTO nodes VALUES('TG','GG');
  INSERT INTO nodes VALUES('GC','CA');
  INSERT INTO nodes VALUES('CG','GT');
  </code></pre>
  
  <p>Visual representation:
  <a href="http://esser.hopto.org/temp/image1.JPG" rel="nofollow">http://esser.hopto.org/temp/image1.JPG</a></p>
  
  <p>Using this data set, I want a user to enter a level (e.g. 2) and this returns all nodes 2 "hops" away from a specific node):</p>
  
  <pre><code>NODE_FROM  NODE_TO
  
  TG        GC
  TG        GG
  AT        TG
  GT          TG
  </code></pre>
  
  <p><a href="http://esser.hopto.org/temp/image2.JPG" rel="nofollow">http://esser.hopto.org/temp/image2.JPG</a></p>
  
  <p>My current attempt looks like this:</p>
  
  <pre><code>SELECT node_from, node_to
    FROM nodes
    WHERE level &lt;= 2   -- Display nodes two "hops" from 'AT'
  START WITH node_from = 'AT'
  CONNECT BY NOCYCLE PRIOR node_to = node_from
      OR    node_to = PRIOR node_from
  GROUP BY node_from, node_to;
  </code></pre>
  
  <p><a href="http://esser.hopto.org/temp/image3.JPG" rel="nofollow">http://esser.hopto.org/temp/image3.JPG</a></p>
  
  <p>As you can see, the relationship: GT -> TG is missing.</p>