>Thank you for the example.

You're welcome. If you want to learn about CTE (common table expressions), take a look at my blog post
CTE and hierarchical queries

It contains links to several other good blogs on the topic of CTE.