SQL query : employee relationships-CodePudding

I have a table called "employment" which looks like

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

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

Thank you

CodePudding user response：

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

CodePudding user response：

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