MySQL 嵌套集检索路径与 join

Question

Evening

我有两个表：urls & places。为了简化起见，数据如下所示：

网址：

urlID    url
1        /england
2        /scotland
3        /wales

地方：

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

然后，地点表将更多地点嵌套在国家/地区内。我希望能够连接两个表以选择我的位置路径，例如：

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

我首先尝试了这个，它不起作用，其他各种方法也没有。

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

您需要在父节点上加入：

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