Recursive joins

I am incredibly lost with recursive joins. I think what I don’t understand is how to use this method to retrieve other bits of information about employees in this mission: https://app.dataquest.io/m/189/intermediate-joins-in-sql/5/recursive-joins. For example, how would I get an employee’s name and birthdate?

Hello @Willyjgolden,

I will try to explain how the recursive join works and how you can choose which table to look for information, since you will be joining similar tables together.

Your main table is FROM employee e1. The employee table has two columns that contain employee id information: employee_id and reports_to.

When you join with another table employee e2, you have to connect both tables with a key. One of the tables would be modified. I have assummed that employee e1 is my main table. I do not want this table to be modified. To ensure this, I have to use the key reports_to for employee e1. The key for the second table, employee e2 would be employee_id and this table e2 would be modified to fit the key reports_to in e1.

To see the modification, the e1.reports_to is the same as the e2.employee_id column.

To get other information about all employee like birthdate etc. You should take from table e1

Therefore, the information about all employees are preserved in e1. While the modified table e2 only contains information about supervisors.

Try the code below

SELECT e1.employee_id,  
       e1.first_name ||" " || e1.last_name employee_name,
       e1.birthdate,
       e1.reports_to,
       e2.employee_id,
       e2.first_name  || " " || e2.last_name supervisor_name,
       e2.reports_to

FROM employee e1
LEFT JOIN employee e2 ON e1.reports_to = e2.employee_id;
5 Likes

Thank you, I appreciate the help!

1 Like

I haven’t made it to the SQL portion of my path yet but this looks like an amazing answer @monorienaghogho!

If you feel like he answered your question @Willyjgolden, maybe you could “show the love” by marking his reply as being the solution to your question. It should appear as a little check-box just below his post (only you can see it because you created the topic) .

2 Likes

With a search, you can find a lot of explanations in existing QnA: https://community.dataquest.io/search?q=recursive%20join which can address this question and more.