Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

Recursive Queries in PostgreSQL

Introduction
The very basics
Infinite looping
Processing trees with recursive CTEs
Processing graphs with recursive CTEs
Summary

Instruction

Excellent! The final step in the query is ahead of you.

Exercise

Now comes the hardest part. You are given the query from the previous exercise, but you need to transform it so that it goes down to every employee in the path. As a result, we want to see each employee in the company with their data and the path from the boss to that person.

Stuck? Here's a hint!

Create a new recursive CTE called hierarchy. The content of the boss CTE from the template should become the anchor member. Then, use UNION ALL and modify the external query from the template so that it uses recursion. Remember that the ID of the superior must match the ID that you got from the previous recursive step. As the number of subordinates in our table is finite, the query will eventually stop. In other words, the termination check is not explicitly provided.

In the outer query, simply select all the information from your recursive CTE.

Don't forget that you start your recursive queries with WITH RECURSIVE.