5. Recursive Joins

In the example, I’m not understanding the logic and outcome of the recursive join, in detail.

Obviously, it joins to itself, but we’re joining on different columns and not the same columns?

INNER JOIN employee e2 on e1.reports_to = e2.employee_id

I’m not understanding how, for instance, “e2.employee_id = 2” is supposed to equal “e1.reports_to = 1” or vice versa

SELECT
    e1.employee_id,
    e2.employee_id supervisor_id
FROM employee e1
INNER JOIN employee e2 on e1.reports_to = e2.employee_id
LIMIT 4;
1 Like

Ah, I see what’s going on. Anyone can chime in and add detail, but basically, all of the reports_to have an employee_id. Therefore, all values in the “reports_to” column exist in the “employee_id” column.

Wish that was stated to make it more clear what exactly was happening.

In the line:

SELECT
e1.employee_id,
e2.employee_id supervisor_id

The e2.employee_id supervisor_id might be clearly stated as e2.employee_id AS supervisor_id. So, e2.employee_id is meant to be the supervisor’s ID as a supervisor for a company is also an employee of a company.

So, the line:

INNER JOIN employee e2 on e1.reports_to = e2.employee_id

is the link between an employee and his/her supervisor.

Hope this helps clear the confusion in the recursive SQL statement.