title: Path enumeration tree model with MySQL
body: <p>I'd like to use the <a href="http://www.onlamp.com/pub/a/onlamp/2004/08/05/hierarchical_sql.html" rel="nofollow">Path Enumeration</a> hierarchical model in my DB schema. 
  The problem is that MySQL doesn't allow me to have a subquery on the same table to retrieve the parent row path. </p>
  
  <p>Is there any way to work around this problem without creating a temporary table?</p>
  
  <p>Here is the DDL for my table:</p>
  
  <pre><code>CREATE TABLE COC_FIELD
  (
      field_id VARCHAR(255) PRIMARY KEY NOT NULL,
      name VARCHAR(255),
      query_id VARCHAR(255) NOT NULL,
      path_enumeration VARCHAR(1000),
      FOREIGN KEY ( report_query_id ) REFERENCES COC_QUERY ( query_id )
  );
  </code></pre>
  
  <p>Statement to insert a child row (not allowed in MySQL):</p>
  
  <pre><code>insert into COC_FIELD(field_id, name, report_query_id, path_enumeration)
  values('field_2', 'Month', 'query_1', CONCAT(
      (select path_enumeration from COC_FIELD where field_id = 'field_2'),
      '/', 'field_3'))
  </code></pre>