Search code examples
sqlself-join

What's wrong with this self join query?

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?

Solution

  • 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