title: SQL to create a hierarchical result-set
body: <p>I have a customer table, with an ID column and a parentId column.
  Whereby the parentId is a FK to the ID of another customer.</p>
  <p>Due to the FK, the ROOT customer is a parent of itself.</p>
  <p>With the following SQL I am attempting to create a hierarchical result-set - but it is not working.</p>
  <p>It is giving me a never-ending cascade (2001 rows), despite only having 2 customers in the table, where one is a parent and one is a child...
  and so I was expecting to receive ONLY 2 rows in total.</p>
  <pre><code>WITH RECURSIVE CustomerTree AS (
          SELECT id as customerId, displayName, IFNULL(customerParentId,0) AS customerParentId, 1 as customerlevel
          FROM customer c1 WHERE c1.id = 1
  
          UNION ALL
  
          SELECT c2.id as customerId, c2.displayName, c2.customerParentId, ct.customerlevel + 1 as customerlevel
          FROM customer c2
          INNER JOIN CustomerTree ct ON c2.customerParentId = ct.customerId
      )
      SELECT customerId, displayName, customerParentId, customerlevel
      FROM CustomerTree
      ORDER BY customerlevel, displayName;
  </code></pre>
  <p>results:</p>
  <pre><code>&quot;customerId&quot;    &quot;displayName&quot;    &quot;customerParentId&quot;    &quot;customerlevel&quot;
  &quot;1&quot;    &quot;PARENT_CO&quot;    &quot;1&quot;    &quot;1&quot;
  &quot;23&quot;    &quot;CHILD_CO&quot;    &quot;1&quot;    &quot;2&quot;
  &quot;1&quot;    &quot;PARENT_CO&quot;    &quot;1&quot;    &quot;2&quot;
  &quot;23&quot;    &quot;CHILD_CO&quot;    &quot;1&quot;    &quot;3&quot;
  &quot;1&quot;    &quot;PARENT_CO&quot;    &quot;1&quot;    &quot;3&quot;
  &quot;23&quot;    &quot;CHILD_CO&quot;    &quot;1&quot;    &quot;4&quot;
  &quot;1&quot;    &quot;PARENT_CO&quot;    &quot;1&quot;    &quot;4&quot;
  &quot;23&quot;    &quot;CHILD_CO&quot;    &quot;1&quot;    &quot;5&quot;
  &quot;1&quot;    &quot;PARENT_CO&quot;    &quot;1&quot;    &quot;5&quot;
  &quot;23&quot;    &quot;CHILD_CO&quot;    &quot;1&quot;    &quot;6&quot;
  &quot;1&quot;    &quot;PARENT_CO&quot;    &quot;1&quot;    &quot;6&quot;
  &quot;23&quot;    &quot;CHILD_CO&quot;    &quot;1&quot;    &quot;7&quot;
  ...
  ...
  </code></pre>
  <p>I would really appreciate any thoughts you might have.
  Thanks.</p>