SQL Query joins tables and returns all records with dynamic id

I have two tables: staffs and teams. Here are their properties:

 # Staffs Table
 id | name
  1 | john
  2 | bob
  3 | alex
  4 | george


 # Teams Table
 id | name   | supervisor_id
  1 | a-team   1
  2 | b-team   1
  3 | c-team   2 
  4 | d-team   3

I want to write a query that returns all the staff id’s records who are supervisors. I know how to join the table but then I don’t know how to finish out the query. I also do not want duplicates.

In the above example data: the staff ids for “john”, “bob” and “alex” should all be returned once. The staff id for “george” should not be returned because he is not a supervisor.

SELECT distinct(staffs.id) 
FROM staffs 
INNER JOIN teams
ON staffs.id = teams.supervisor_id
WHERE ??? ;

Answer

Would be easier to do with a subquery in the where clause

SELECT distinct(staffs.id) 
FROM staffs 
WHERE staffs.ID IN (select supervisor_id from Teams)

EDIT: SQL Fiddle

Advertisement

Answer