4. Understanding the Elements of SQL Statements : Common Table Expressions (CTEs) : Examples of Using CTEs: The Person and Relative Tables
 
Share this page                  
Examples of Using CTEs: The Person and Relative Tables
The CTE examples in this section assume the following data in two tables:
Person table
 
 
Relatives table
 
Name
Gender
Name
Parent
Me
M
Me
Mom
Sis
F
Me
Dad
Dad
M
Sis
Mom
Mom
F
Sis
Dad
Bro
M
Bro
Mom
StSis
F
Bro
Dad
Uncle1
M
Dad
GrDad
Uncle2
M
Dad
GrMa
Aunt
F
StSis
Dad
GrDad
M
StSis
StMom
GrMa
F
Uncle1
GrDad
GrGrDad
M
Uncle1
GrMa
GrGrMa
F
Uncle2
GrDad
Son
M
Uncle2
GrMa
GrSon
M
Aunt
GrDad
Daugtr
F
Aunt
GrMa
GrDaugtr
F
GrDad
GrGrDad
Dog
F
GrDad
GrGrMa
StMom
F
Son
Me
Niece
F
GrSon
Son
Nephew
M
Daugtr
Me
Cousin
M
GrDaugtr
Son
 
 
Niece
Sis
 
 
Nephew
Bro
 
 
Cousin
Uncle1
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