Ketus Ketus - 6 months ago 26
SQL Question

SELECT with temporary column with value based on other column

I have mysql table which schema resembles the json array below.

[
{ "id": 1, "firstName": "Charles Montgomery", "lastName": "Burns", "managerId": 12},
{ "id": 2, "firstName": "Bart", "lastName": "Simpson", "managerId": 1},
{ "id": 3, "firstName": "Marge", "lastName": "Simpson", "managerId": 1},
{ "id": 4, "firstName": "Lisa", "lastName": "Simpson", "managerId": 1},
{ "id": 5, "firstName": "Maggie", "lastName": "Simpson", "managerId": 1},
{ "id": 6, "firstName": "Homer", "lastName": "Simpson", "managerId": 4},
{ "id": 7, "firstName": "Ned", "lastName": "Flanders", "managerId": 4},
{ "id": 8, "firstName": "Krusty", "lastName": "The Clown", "managerId": 2},
{ "id": 9, "firstName": "Waylon", "lastName": "Smithers", "managerId": 2},
{ "id": 10, "firstName": "Ralph", "lastName": "Wiggum", "managerId": 5},
{ "id": 11, "firstName": "Itchy", "lastName": "", "managerId": 5},
{ "id": 12, "firstName": "Comic Book Guy", "lastName": "", "managerId": 4}
]


managerId
field is self referencing foreign key to the
id
field.

I need to make a query which will add column on the fly with "managerName" computed from
id
field which will hold concatenated firstName and lastName.

Below is (simplified) query that I've tried. Question mark is placeholder filled by mysql driver (in nodejs):

SELECT * ,
(SELECT CONCAT_WS(' ', firstName, lastName) FROM employee WHERE managerId = id ) as managerName
FROM employee WHERE employee.id = ?;


I realize this
WHERE managerId = id
is the place to look, but I honestly don't know what condition to place to match correct values.

Can anyone help?

Answer

LEFT JOIN is your friend. tra to use a query like this:

SELECT 
    e.*,
    CONCAT_WS(' ', m.firstName, m.lastName) AS managerName
FROM employee e WHERE e.employee.id = ?
LEFT JOIN  employee m ON e.managerId = m.id;
Comments