Amit Amit - 5 months ago 13
SQL Question

Mysql self join not giving accurate data

I have employee table like below.

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


I want LIKE query with key word
Nancy
and it should return result like below(parent record with name like Nancy and it's children records.).

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


try with following query but it is return only child records not the parent record having
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

try with if null on join 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%'