Mysql how to return specific query result?-CodePudding

I have a table with a column of either intergers or NULL, the integers are referencing the primary key of the same table. I'm trying to query and get a result of a column that has names with the primary key of the integers and retains the NULL if present.

CURRENT TABLE

id name   manager_id
1  mike   5
2  lisa   3
3  tom    NULL
4  andy   3
5  brian  NULL

EXPECTED RESULT

id  name  manager_id
1   Mike  Brian
2   Lisa  Tom
3   Tom   NULL
4   Andy  Tom
5   Brian NULL

CodePudding user response：

You can LEFT JOIN the table with itself. For example:

select e.id, e.name, m.name
from t e
left join t m on m.id = e.manager_id

Result:

 id  name   name  
 --- ------ ----- 
 1   mike   brian 
 2   lisa   tom   
 3   tom    <null>
 4   andy   tom   
 5   brian  <null>

See running example at db<>fiddle.

CodePudding user response：

Use a UNION of two queries. One returns the NULL values, the other uses a self-join to get the names from the referenced rows.

SELECT t1.col1, t1.col2, ..., t2.name
FROM yourTable AS t1
JOIN yourTable AS t2 ON t2.id = t1.parent
WHERE t1.parent IS NOT NULL

UNION ALL

SELECT t1.col1, t1.col2, ..., t1.parent
FROM yourTable AS t1
WHERE t1.parent IS NULL

CodePudding user response：

CREATE VIEW AS SELECT e1.emp_Id EmployeeId, e1.emp_name EmployeeName,
e1.emp_mgr_id ManagerId, e2.emp_name AS ManagerName FROM tblEmployeeDetails e1 LEFT JOIN tblEmployeeDetails e2 ON e1.emp_mgr_id = e2.emp_id