CODEWITHSUNDEEP
mysqlsql
hyunseo2
hyunseo2

Reputation: 41

SQL query : employee relationships

I have a table called "employment" which looks like

enter image description here

if the boss column is empty it means he/she is the "CEO"

and if he/she manages one another under boss column it means "Manager"

else it's "Worker"

Finally it should look like

enter image description here

Can you help build some query to make the following result?

Thank you

Upvotes: 0

Views: 191

Answers (2)

Ojaswi Awasthi
Ojaswi Awasthi

Reputation: 31

So, this can be solved using the case expression.

select Name,
case 
    when BOSS = "" then "CEO"
    when exists(select NULL from employment t2 where t1.NAME = t2.BOSS) then "MANAGER"
    else "WORKER"
end as BOSS
from Employment t1

Upvotes: 0

Akina
Akina

Reputation: 42641

SELECT name,
       CASE WHEN boss = ''   -- or maybe WHEN boss IS NULL 
            THEN 'CEO'
            WHEN EXISTS (SELECT NULL FROM employment t2 WHERE t1.name = t2.boss)
            THEN 'MANAGER'
            ELSE 'WORKER'
            END posession
FROM employment t1

Upvotes: 1

Related Questions