title: SQL - Getting a single branch of a hierarchy
body: <p>I have the following table "x" of hierarchical attributes:</p>
  
  <pre><code>+-----------+--------+--------------+-----------+-------------+
  | row       | value  | displayvalue | parentrow | parentvalue |
  +-----------+--------+--------------+-----------+-------------+
  | HIE1      | VALUE3 | value 3      | NULL      | NULL        |
  | HIE2      | VALUE1 | value 11     | HIE1      | VALUE1      |
  | HIE2      | VALUE1 | value 21     | HIE1      | VALUE2      |
  | HIE2      | VALUE1 | value 31     | HIE1      | VALUE3      |
  | HIE3      | VALUE1 | value 111    | HIE2      | VALUE1      |
  | HIE3      | VALUE1 | value 311    | HIE2      | VALUE1      |
  | HIE3      | VALUE1 | value 221    | HIE2      | VALUE2      |
  +-----------+--------+--------------+-----------+-------------+
  </code></pre>
  
  <p>This is just an example - the hierarchy might be bigger or smaller, and the row codes do not have to be numerical. How do I go about extracting the only correct branch in this table? i.e.:</p>
  
  <pre><code>+-----------+--------+--------------+-----------+-------------+
  | row       | value  | displayvalue | parentrow | parentvalue |
  +-----------+--------+--------------+-----------+-------------+
  | HIE1      | VALUE3 | value 3      | NULL      | NULL        |
  | HIE2      | VALUE1 | value 31     | HIE1      | VALUE3      |
  | HIE3      | VALUE1 | value 311    | HIE2      | VALUE1      |
  +-----------+--------+--------------+-----------+-------------+
  </code></pre>
  
  <p>This will require recursion, and I've tried:</p>
  
  <pre><code>With Attrs AS (
      SELECT "row",
             "value",
             "displayvalue",
             "parentrow",
             "parentvalue",
       FROM x
  
      UNION ALL
  
      SELECT child."row",
             child."value",
             child."displayvalue",
             child."parentrow",
             child."parentvalue",
       FROM x child
       JOIN Attrs ON child."parentrow" = Attrs."row" AND child."parentvalue" = Attrs."value"
       WHERE Attrs."parentrow" IS NOT NULL
  
    )
    SELECT "row", "value", "displayvalue", "parentrow", "parentvalue"
    FROM Attrs;
  </code></pre>
  
  <p>But this seems to expand the result pool rather than narrowing it down. What am I doing wrong?</p>
  
  <p><strong>EDIT</strong></p>
  
  <p>Forgot to add the sequence columns in the table. These are the extra entries:</p>
  
  <pre><code>+-----------+--------+-----------+
  | row       | seqno  | parentseq |
  +-----------+--------+-----------+
  | HIE1      | 3      | NULL      |
  | HIE2      | 10     | 1         |
  | HIE2      | 13     | 2         |
  | HIE2      | 11     | 3         |
  | HIE3      | 15     | 10        |
  | HIE3      | 16     | 11        |
  | HIE3      | 17     | 12        |
  +-----------+--------+-----------+
  </code></pre>
  
  <p>The desired rows would have:</p>
  
  <pre><code>+-----------+--------+-----------+
  | row       | seqno  | parentseq |
  +-----------+--------+-----------+
  | HIE1      | 3      | NULL      |
  | HIE2      | 11     | 3         |
  | HIE3      | 16     | 11        |
  +-----------+--------+-----------+
  </code></pre>