Examples of Using CTEs: The Person and Relative Tables

Share this page

The CTE examples in this section assume the following data in two tables:

With this data we can perform simple lookups that can be used to answer relationship questions.

Q: Who is the mother of whom?

To answer who is the mother of whom we can write:

WITH

ismother(name, child) AS (

SELECT parent,r.name

FROM person p JOIN relatives r ON p.name=r.parent AND gender='F'

)

SELECT * FROM ismother

ORDER BY 1,2;

The ismother result set is returned:

ismother

name child

------ -------

GrGrMa GrDad

GrMa Aunt

GrMa Dad

GrMa Uncle1

GrMa Uncle2

Mom Bro

Mom Me

Mom Sis

Sis Niece

StMom StSis