Have you ever faced the need to create some hierarchical query?
Today I’ll show you how to create this using Common Table Expressions (CTE).
What’s Common Table Expressions?
CTEs works like derived tables, but with the possibility of being self-referenced which is just what we need to do hierarchical queries, for example (but not only that, okay?).
Ok, now show me how that works!
First you need to know the structure and it’s simple like that:
with [Name of CTE] as ( [Query to mount the CTE] ) [Query to query the CTE (sorry for that!)]
Yes. Just it.
Now, let’s think about this table called Person:
As you can see, we have three columns: id, name and a relationship with itself called father.
Now, if we want to see all children of Oliver, we can write something like this:
select person.name from person join person father on person.father = father.id where father.name = 'Oliver'
And the result will be William and Jack.
But if we want to see all of William’s offspring, for example?
We can do that:
with cte as ( -- Selecting father William select id, name, father from person father where name = 'William' union all -- Selecting all William's Children select child.id, child.name, child.father from person child join cte on child.father = cte.id ) select id, name, father from cte
And we can realize that William had Noah that had Ethan.
Now i’ts your turn!
Let us know if it was useful to you! 🙂