Bro Nick Bro Nick - 1 month ago 7
MySQL Question

SQL Query to display employee who is a supervisor from employee table

I am in this dilemma:
Here is a table with records.
The last collumn has supervisors and it refers to the first collumn.
How can I select all employees who are supervisors and display for each, first name and last name:
Here is the table

enter image description here

Answer

You can use EXISTS to do this(preferred approach)

SELECT *
FROM   yourtable t1
WHERE  EXISTS (SELECT 1
               FROM   yourtable t2
               WHERE  t1.EMPLOYEEID = t2.EMPSUPERVISOR) 

using IN operator

SELECT *
FROM   yourtable t1
WHERE  t1.EMPLOYEEID IN (SELECT t2.EMPSUPERVISOR
                         FROM   yourtable t2) 

using JOIN

SELECT t1.*
FROM   yourtable t1
       JOIN (SELECT DISTINCT EMPSUPERVISOR
             FROM   yourtable) t2
         ON t1.EMPLOYEEID = t2.EMPSUPERVISOR