mysql - 具有嵌套连接和选择的 SQL 查询

Question

我正在尝试从我的数据库中的菜单项列表中将多层菜单放在一起。这是我的查询。 fl=一级，sl=二级，以此类推。

这是查询:

SELECT name as flName,id AS flId
FROM menu_items AS m1
WHERE parent_id = 0
INNER JOIN (SELECT name AS slName, id AS slId, parent_id AS slPid FROM menu_items)
m2 ON m1.firstLevelId = m2.parent_id
INNER JOIN (SELECT name AS tlName, id AS tlId, parent_id AS tlPid FROM menu_items)
m3 ON m2.id = m3.parent_id

我得到的错误是这样的:

PersistenceException: Query threw SQLException:You have an error in your SQL syntax.  
check the manual that corresponds to your MySQL server version for the right syntax to 
use near 'INNER JOIN (SELECT name AS slName, id AS slId, parent_id AS slPid FROM  
menu_item'

但它对我来说似乎有效，我不知道.. 很确定我以前在子查询上做过连接。我做错了什么吗？

Answer 1

将 where 子句移到末尾，如下所示:

SELECT name as flName,id AS flId
FROM menu_items AS m1 INNER JOIN (
        SELECT name AS slName, id AS slId, parent_id AS slPid FROM menu_items
    ) m2 ON m1.firstLevelId = m2.parent_id
    INNER JOIN (
        SELECT name AS tlName, id AS tlId, parent_id AS tlPid FROM menu_items
    ) m3 ON m2.id = m3.parent_id
WHERE parent_id = 0