mysql - 有没有办法在 sql 中获取 self 表的父级？

Question

我需要一些东西 like this在mysql数据库中。

在我的例子中，我需要从:

ID | FIRST_REFERENCE  | description
1  | -1               | root
2  | 1                | other_child
3  | -1               | root
4  | 2                | child
5  | 1                | other_child
6  | 3                | child

我需要返回带有 childID 的根，如果 childID 引用根，则返回子:

我试过这个:

SELECT parent.ID 
FROM mytable as child
JOIN mytable as parent
ON child.FIRST_REFERENCE = parent.ID 
WHERE parent.FIRST_REFERENCE = '-1' AND child.ID = '6'

但是没用

然后我尝试了一个带有 select case 的解决方案:

  SELECT
   CASE FIRST_REFERENCE
   WHEN '-1' THEN ID
   ELSE SELECT rec.ID 
       FROM mytable as rec 
       WHERE rec.ID = main.FIRST_REFERENCE
   END as 'root'
  FROM mytable as main
  WHERE childID = '6'

但是我得到一个语法错误。

我哪里错了？

Answer 1

如果其他人遇到我的问题我会微笑着解决 LEFT JOIN:

SELECT CASE WHEN parent.ID is NULL THEN child.ID
        ELSE parent.ID
        END AS ID
FROM my table AS child 
LEFT JOIN my table AS parent ON 
  ((parent.ID = child.FIRST_REFERENCE AND parent.FIRST_REFERENCE =  '-1') OR 
  (child.FIRST_REFERENCE = '-1' AND parent.ID = NULL)) 
WHERE child.ID =  '6'