Mysql how to return specific query result?

Question

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

I feel like I'm on the brink of understanding the question. Can you add a few rows of sample data and the expected result? — The Impaler, Jul 25 '22 at 15:47
We need to see some sample data before we can tell you for sure. — Andy Lester, Jul 25 '22 at 15:50
You can follow this link to get your solution https://stackoverflow.com/questions/11427300/self-join-to-get-employee-manager-name — Raunak, Jul 25 '22 at 16:41

score 1 · Answer 1 · answered Jul 25 '22 at 17:05

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.

score 0 · Answer 2 · answered Jul 25 '22 at 16:07

0

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

answered Jul 25 '22 at 16:07

Barmar

741,623
53
500
612

1

I get we don't have all of the information in the OP, but surely a self-joined `LEFT OUTER JOIN` would be advisable over a union here? – Rogue Jul 25 '22 at 16:20
That's another way to do it. – Barmar Jul 25 '22 at 16:27

score 0 · Answer 3 · answered Jul 25 '22 at 16:43

0

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

answered Jul 25 '22 at 16:43

Raunak

1
1

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Jul 27 '22 at 04:15

Mysql how to return specific query result?

3 Answers3