Why do we need recursive Joins?

Q&A DQ Courses
#1

Hi there,
I just cannot undertsand we need to make a recursive join to get info on 2 columns within one same table as in lesson 5/9 within the “Intermediate Joins in SQL” mission. Instead of using the proposed query:

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;

Why not use a query like the one below?

SELECT
employee_id,
reports_to supervisor_id
FROM employee
LIMIT 4

Thanks in advance for any clues!

1 Like
#2

This particular query only selects the employee_id from e2, so it looks like a redundant join.
However, if you were to look for additional information about the supervisor, for example, the name of the supervisor, then you will need the recursive join, since the only information about the supervisor in e1 is the id.

To illustrate this example:

SELECT
e1.employee_id,
e2.employee_id supervisor_id,
e1.first_name employee_name,
e2.first_name supervisor_name
FROM employee e1
INNER JOIN employee e2 on e1.reports_to = e2.employee_id
LIMIT 4;

Hope this helps!

1 Like
#3

Great. I think the initial example of the lesson was limited in exposing this but now I got it. Thank you!