MySQL Nested Set Retrieving Path with Join

Question

Evening

I have two tables: urls & places. To simplify, the data is like this:

URLS:

urlID    url
1        /england
2        /scotland
3        /wales

PLACES:

placeId    name        lft    rgt       urlRef
1          England     1      23000     1
2          Scotland    23001  37000     2
3          Wales       37001  50000     3

The places table then has further places nested inside the countries. I'd like to be able to join the two tables to select a path of my places like:

England     /england
Shropshire  /england/shropshire
Shrewsbury  /england/shropshire/shrewsbury

I first tried this, which doesn't work and various other methods haven't either.

SELECT node.name, url 
FROM places AS node, places AS parent LEFT JOIN urls ON urlId = node.urlRef
WHERE node.lft BETWEEN parent.lft AND parent.rgt AND urlId = 1
ORDER BY node.lft

Answer 1

You need to join on the parent node:

SELECT parent.placeId, parent.name, parent.type, parent.ico, url
FROM places AS node, urls LEFT JOIN places AS parent ON parent.urlRef = urls.urlId
WHERE node.lft BETWEEN parent.lft AND parent.rgt AND node.urlRef = :urlId
ORDER BY parent.lft