title: Get tree path in MySQL table
body: <p>Perhaps the easiest approach to manage hierarchical data in MySQL databases is the <a href="http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/" rel="nofollow">adjacency list model</a>. It is, give to every node a parent:</p>
  
  <pre><code>CREATE TABLE category(
  category_id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(20) NOT NULL,
  parent INT DEFAULT NULL);
  </code></pre>
  
  <p>It is easy to get the parent node, or even if there is a maximum tree depth, you can get the whole tree using this: </p>
  
  <pre><code>SELECT CONCAT_WS('/', `t3`.`name`, `t2`.`name`, `t1`.`name`) AS `path`
  FROM category AS t1
  LEFT JOIN category AS t2 ON t2.parent = t1.category_id
  LEFT JOIN category AS t3 ON t3.parent = t2.category_id
  LEFT JOIN category AS t4 ON t4.parent = t3.category_id
  WHERE t1.name = 'xxxxx';
  </code></pre>
  
  <p>That's enough in many cases, but how can you generalize this solution for trees deeper than 3 nodes? I.e. You may have a path like "Electronics/Audio/Transmiter/FM/Motorola".</p>
  
  <p>Is it possible with only one query?</p>