CODEWITHSUNDEEP
sql
Ash S
Ash S

Reputation: 119

How to add and modify a column in SQL that is specific to the table

I have the following table

Name    ReportsTo   Position
-----------------------------
Jill    null        manager
Nathan  Jill        junior

I want to add another column in the end that says the title of the person they report to so it would look like this.

Name    ReportsTo   Position  Boss
-------------------------------------
Jill    null        manager   none
Nathan  Jill        junior    manager

My current code is (to get the first table I displayed)

SELECT 
    t1.Name, t1.ReportsTo, t1.Position 
FROM 
    yourTable t1 
WHERE 
    ReportsTo = 'Jill' OR ReportsTo IS NULL

Upvotes: 0

Views: 38

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521259

To bring in the immediate managers of each employee you may use a self join:

SELECT t1.Name, t1.ReportsTo, t1.Position, COALESCE(t2.Position, 'none') AS Boss
FROM yourTable t1
LEFT JOIN yourTable t2
    ON t2.Name = t1.ReportsTo;

Demo

Upvotes: 2

Related Questions