Ketus Ketus - 1 year ago 81
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}

field is self referencing foreign key to the

I need to make a query which will add column on the fly with "managerName" computed from
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 CONCAT_WS(' ', firstName, lastName) FROM employee WHERE managerId = id ) as managerName
FROM employee WHERE = ?;

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 Source

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

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