Self-Join in Sheets, SQL, Python, and JavaScript

Let's start with a self-referencing data table employees:

id  name                 manager_id
1   Djan Seriy Anaplian
2   Fal 'Ngeestra
3   Turminder Xuss       1           --->  Djan Seriy Anaplian
4   Jase                 2           --->  Fal 'Ngeestra

How can we get the other record?

Sheets

As with so many questions in Excel, Numbers, Calc, or Sheets, let's solve it by adding a column with an INDEX and MATCH formula:

# 1. find row number of the matching record
=match(C2, A:A, 0)

# 2. get value from the other record
=index(B:B, match(C2, A:A, 0))

# 3. handle errors
=iferror(index(B:B, match(C2, A:A, 0)), "")

You can try out the example in Sheets:

SQL

In virtually all database query languages, joins to a table itself work exactly like joins between tables:

select
  e.name employee,
  m.name manager
from employees e
left join employees m on e.manager_id = m.id

Try out in SQL Fiddle:

Python

To represent the "normal form" of our data in Python, let's choose a dictionary:

employees = {
    1: {'name': 'Djan Seriy Anaplian'},
    2: {'name': 'Fal \'Ngeestra'},
    3: {'name': 'Turminder Xuss', 'manager_id': 1},
    4: {'name': 'Jase', 'manager_id': 2},
}

We want to change it to a list of nested dictionaries, replacing each manager_id pointer with a live manager object, to give ourselves the most freedom to explore. Using imperative procedural code style with a walrus operator := and dict.pop(), that might look like following:

for em in employees.values():
    if manager_id := em.pop('manager_id', None):
        em['manager'] = employees[manager_id]

employees = list(employees.values())
employees[0]['short'] = 'Anaplian'
employees[1]['short'] = 'Fal'

This example lives on Repl.it. Will it work for a deeper management hierarchy? Would it break for a cyclical structure?

Let me know if you would like to see other code styles in Python, functional like JS below or FRP like the Sheets above (with Pandas).

JavaScript / TypeScript

We can start with the exact same employees object as in the Python example, but this time we choose a more team-friendly approach inspired by functional programming style that avoids mutation and side effects, because the nested objects might be used in other parts of the project. We also want to avoid too many levels of nesting to keep it simple, so we replace manager_id with an optional immutable string managerName:

const employeesWithManager = Object.values(employees)
  .map(({name, manager_id}) => ({
    name,
    ...(manager_id && {
      managerName: employees[manager_id].name
    }),
  }))

You can play with a TypeScript version on TS Playground, or run the JavaScript example directly in your browser console:

Have you noticed how we can use the spread syntax in object literal ({...expr}) with a more complex expression, not just a variable name? Can you refactor the code to use an explicit variable at that place? Which version is more readable for you and for your project coworkers?

👋 Hi, I am Peter Hozák, a.k.a. Aprillion. Do you have any follow-up or similar questions? Let's discuss in the comments below or on Twitter.