Question 22038 in Mysql Question posted in
The official MySQL documentation can be found here.

SQL query : employee relationships – Mysql

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

Tags:

2

Answers


  1. 0 
    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
    Login or Signup to reply.
  2. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search