How does this mySQL statement retrieve a single path?

Question

I'm reading this article and I don't understand how this query works:

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;

I'm trying to understand how it works because I need to modify it slightly for my program.

I have a data stored in a hierarchical database using the nested set model.

It can be visualized like this:

    link
     |
   saria
     |
    roy
     |
   olimar
   /   \
blue    red
 |       |
same    same

When given an array like this

['link', 'saria', ,'roy', 'olimar', 'blue', 'same']

I want to return the name/attributes of only the elements along this path.

When I use the above query modified slightly to this:

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

I will get a table like this:

| 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  |

but I want a table like this:

| file_id | name | owner | 
|    1    | link |  dez  |
|    2    | saria|  madi |
|    3    | roy  |  wrm  |
|    4    | oli  |  mart |
|    5    | blue |  bob  |
|    6    | same |  jim  |

I need to a way to go along the path given in the array so I can get to the correct 'same' node.

Answer 1

Your variation does not work because "same" is not unique, as assumed by the original query.

Try with

WHERE (...) node.id = 6 -- or 8

instead of

WHERE (...) node.name = 'same'