title: How does this mySQL statement retrieve a single path?
body: <p>I'm reading this <a href="http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/" rel="nofollow">article</a> and I don't understand how this query works:</p>
  
  <pre><code>SELECT parent.name
  FROM nested_category AS node,
      nested_category AS parent
  WHERE node.lft BETWEEN parent.lft AND parent.rgt
      AND node.name = 'FLASH'
  ORDER BY node.lft;
  </code></pre>
  
  <p>I'm trying to understand how it works because I need to modify it slightly for my program. </p>
  
  <p>I have a data stored in a hierarchical database using the nested set model. </p>
  
  <p>It can be visualized like this:</p>
  
  <pre><code>    link
       |
     saria
       |
      roy
       |
     olimar
     /   \
  blue    red
   |       |
  same    same
  </code></pre>
  
  <p>When given an array like this</p>
  
  <blockquote>
    <blockquote>
      <p>['link', 'saria', ,'roy', 'olimar', 'blue', 'same']</p>
    </blockquote>
  </blockquote>
  
  <p>I want to return the name/attributes of only the elements along this path. </p>
  
  <p>When I use the above query modified slightly to this:</p>
  
  <pre><code>SELECT DISTINCT parent.file_id, parent.owner, parent.name
  FROM file_category AS node, file_category AS parent 
  WHERE node.lft BETWEEN parent.lft AND parent.rgt 
  AND node.name = 'same' order by parent.lft
  </code></pre>
  
  <p>I will get a table like this:</p>
  
  <pre><code>| file_id | name | owner | 
  |    1    | link |  dez  |
  |    2    | saria|  madi |
  |    3    | roy  |  wrm  |
  |    4    | oli  |  mart |
  |    5    | blue |  bob  |
  |    6    | same |  jim  |
  |    7    | red  |  tom  |
  |    8    | same |  kim  |
  </code></pre>
  
  <p>but I want a table like this:</p>
  
  <pre><code>| file_id | name | owner | 
  |    1    | link |  dez  |
  |    2    | saria|  madi |
  |    3    | roy  |  wrm  |
  |    4    | oli  |  mart |
  |    5    | blue |  bob  |
  |    6    | same |  jim  |
  </code></pre>
  
  <p>I need to a way to go along the path given in the array so I can get to the correct 'same' node. </p>