Recursive queries

Hello there!

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:

Capturar

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! 🙂

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s