What's wrong with this self join query?

user_5686346 0
Viewed 37

I apologize for such a basic question but I'm new to SQL and can't figure this out somehow. I have a simple table with 3 columns: id, name, and manager_id. Sample entries are:

  • 1, Mike, 3
  • 2, Kate, 3
  • 3, Joe, NULL

I want to return name and the person's manager, e.g. Mike - Joe, Kate - Joe, etc. I'm trying the following code:

SELECT
  uno.name AS employee,
  dos.name AS manager
FROM
    `dataset.workers` AS uno
JOIN `dataset.workers` AS dos 
  ON uno.id = dos.manager_id

and im getting two sets of names but they don't match (for example, I get Mike - NULL, Kate - Mike). How should I tweak the query to get what I need?

self-join sql
1 Answers

Here's the solution. You want to use left join in case someone doesn't have a manager.

select t.name
      ,t2.name as manager
from   t left join t t2 on t2.id = t.manager_id
name manager
Mike Joe
Kate Joe
Joe null

Fiddle

user_191745700
Related