Asking again - Using a select clause to return different values that come from the same atribute - MySQL

Question

I need to select from an employee table the names of those who are supervisors and those who are not. Therefore, I need two separate columns to return, one for the supervisors' names and one for the employees'.

To do that, I have tried using where exists like so

select concat(first_name, middle_name, last_name) as supervisor_name, concat(first_name, middle_name, ulast_name) as employee_name
from employees
where exists (select employee_name from employees where employees.id = department.supervisor_id);

I have also tried creating a union between two select clauses, like so:

select  concat (first_name, middle_name, last_name) as supervisor_name
from employees
where exists (select * from department where employees.id = department.supervisor_id)
union
select  concat (first_name, middle_name, last_name) as employee_name
from femployees
where exists (select * from department where employees.id != department.supervisor_id);

Note that the department is another table in which I have the supervisor's id numbers.

I have searched if I could use some sort of check constraint as an alternative but couldn't find it. I also tried applying select distinct , in an attempt to "divide" the values returned but couldn't make it work as well.

Have also tried using an alias, but it returns that first_namein field is ambiguous. Here is the script:

select concat (first_name, middle_name, last_name) as supervisor_name, concat (first_name, middle_name, last_name) as employee_name
from employees
join employees as supervisor_name on department.supervisor_id = employees.id
join employees as supervisor_id on department.supervisor_id != employees.id;

Answer 1

You have to join with the department table.

You need to use table aliases in the SELECT list to avoid ambiguity.

select concat (s.first_name, s.middle_name, s.last_name) as supervisor_name, concat (e.first_name, e.middle_name, e.last_name) as employee_name
from employees AS e
JOIN department as d ON e.department_id = d.id
join employees as s on d.supervisor_id = s.id