title: Recursive CTE in MySql for tree structure (adjacency list)
body: <p>I'm just starting out with MySQL (I come from using SQL Server previously).  I haven't yet started implementing anything in MySQL, just researching how to do things and what problems I might encounter.</p>
  
  <p>In SQL Server I've used CTEs to successfully recurse through an adjacency list table structure to produce the desired result set.  From what I can tell so far with MySQL, it does not support CTEs.  I've got a fairly simple table structure to hold my hierarchy (written in SQL Server syntax b/c of my familiarity with it):</p>
  
  <pre><code>CREATE TABLE TreeNodes (
      NodeId int IDENTITY(1,1) NOT NULL PRIMARY KEY,
      ParentNodeId int NULL,
      Name varchar(50) NOT NULL,
      FullPathName varchar(MAX) NOT NULL,  -- '/' delimited names from root to current node
      IsLeaf bit NOT NULL  -- is this node a leaf?
  )
  </code></pre>
  
  <p><strong>Side Note</strong>: I realize that FullPathName and IsLeaf are not required and could be determined at query time, but the insert of a tree node will be a very uncommon occurrence as opposed to the queries against this table - which is why I plan to compute those two values as part of the insert SP (will make the queries that need those two values less costly).</p>
  
  <p>With CTE (in SQL Server), I would have a function like the following to find leaf nodes of current node:</p>
  
  <pre><code>CREATE FUNCTION fn_GetLeafNodesBelowNode (
      @TreeNodeId int
  )
  RETURNS TABLE
  AS
  RETURN
  WITH Tree (NodeId, Name, FullPathName, IsLeaf)
  AS (
      SELECT NodeId, Name, FullPathName, IsLeaf FROM TreeNodes WHERE NodeId = @TreeNodeId
          UNION ALL
      SELECT c.NodeId, c.Name, c.FullPathName, c.IsLeaf FROM Tree t
          INNER JOIN TreeNodes c ON t.NodeId = c.ParentNodeId
  )
  SELECT * FROM Tree WHERE IsLeaf = 1
  </code></pre>
  
  <p>How would I do the same with MySQL?</p>
  
  <p>Thanks in advance.</p>