Self-Join in Sheets, SQL, Python, and JavaScript
Tags: programming, tutorial
Published: 2022-07-23 13:27:36

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

```c
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](https://www.excel-easy.com/examples/index-match.html) formula:

```c
# 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](https://docs.google.com/spreadsheets/d/1mfHKy5uThKXGpv6Lu1-00elDaA3ZBQvGK1Ngq6D2FDM):
![google sheets with INDEX and MATCH formula for self-join employees table](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/kqgyc6gd0skkg4ofvdbn.jpg)

## SQL

In virtually all database query languages, joins to a table itself work exactly like [joins between tables](https://www.postgresql.org/docs/current/tutorial-join.html):

```sql
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](http://sqlfiddle.com/#!17/53a00/3):
![left join in sql](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8a5uqjo280jsokil6wvz.jpg)

## Python

To represent the ["normal form"](https://www.geeksforgeeks.org/normal-forms-in-dbms/) of our data in Python, let's choose a dictionary:

```py
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 :=](https://docs.python.org/3/whatsnew/3.8.html#assignment-expressions) and [dict.pop()](https://docs.python.org/3/library/stdtypes.html#dict.pop), that might look like following:

```py
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](https://replit.com/@Aprillion/self-join#main.py). 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](https://en.wikipedia.org/wiki/Functional_reactive_programming) like the Sheets above (with [Pandas](https://pandas.pydata.org/)).

## 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`:

```js
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](https://www.typescriptlang.org/play?#code/C4TwDgpgBAogtmANgexBaBeKBvAUFKAOwEM4IAuKAZ2ACcBLQgc3yjmJKYloH16ATAPyVCAVzgAjbrgC+uAMbJCNKBAQo0EKpQBKERbX4AeMZO4AaWOtToAfFCx4CBAIyVsJMpQDkAEQBWHFAAytz0IFAAgiRI9BzeMuasBABM7p4UUN4AYsSIUAA63gByXFp0xAlJzlAAzOmkmd4AKqK0cIz83FAAGqJUVN6W7JzcfPyULonJUAAsDV5ZAFLEVBBDbBzEXLwClCnTcgpKKmpINloA6vTAABYAsls7DlAA8hL++sAAdABueaItAAKM4adBUACUrG+7DAQKBHkawyeYwEMghDnsCJmGWqzm+BKBI22qP4UAAZOScDMCMSdsVGpRQRcqABtOmkgC63wyM3RePRUOOymQiAg3xQTBB1k0VGud0eo1oUKAA), or run the JavaScript example directly in your browser console:
![js example in browser console](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/si11i17xrym9buylmuct.jpg)

Have you noticed how we can use the [spread syntax](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Operators/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](https://github.com/Aprillion/). Do you have any follow-up or similar questions? Let's discuss in the comments below or on [Twitter](https://twitter.com/aprillion0042).*