Mysql self join 没有给出准确的数据

Question

我有如下员工表。

emp_id      emp_name            emp_supv
4081        Nancy Brown         0
4083        Peter Parker        4081
4055        Jacob Miller        4083
4058        Mary Ray            4083
4060        Jane Smith          4081
4061        Bob Hunter          4081
4066        Nancy Smith         0
4061        Bob Smith           4066

我想用关键字 Nancy 进行 LIKE 查询，它应该返回如下结果(名称为 Nancy 的父记录及其子记录。)。

4081        Nancy Brown       0
4083        Peter Parker      4081
4060        Jane Smith        4081
4061        Bob Hunter        4081
4066        Nancy Smith       0
4061        Bob Smith         4066

尝试使用以下查询，但它只返回子记录而不返回具有 emp_supv 0

的父记录

SELECT sa.id AS id,sa.name AS Name,
sa2.id AS child_id, sa2.name AS child_name
FROM employees AS sa LEFT OUTER JOIN employees AS sa2
ON sa.emp_supv = sa2.emp_id where sa2.emp_name LIKE '%Nancy%';

Answer 1

尝试使用 if null 加入 as

    SELECT sa.emp_id AS id,sa.emp_name AS Name, 
    sa.emp_supv AS child_id, sa2.emp_name AS child_name
    FROM Employees  AS sa 
    LEFT OUTER JOIN employees AS sa2
    ON case when ifnull(sa.emp_supv,0)=0 then sa.emp_id else sa.emp_supv end= sa2.emp_id 
where sa2.emp_name LIKE '%Nancy%'