3. Elements of SQL Statements : Common Table Expressions (CTEs) : Examples of Using CTEs: The Person and Relative Tables : Referring to a Defined WITH Element
 
Share this page                  
Referring to a Defined WITH Element
Q: Who are parents?
To determine who are parents we could simply apply a UNION where we needed it, but we can also define a further element, isparent:
WITH
ismother(name, child) AS (
        SELECT parent,r.name
        FROM person p JOIN relatives r ON p.name=r.parent AND gender='F'
),
isfather(name, child) AS (
        SELECT parent,r.name
        FROM person p JOIN relatives r ON p.name=r.parent AND gender='M'
),
isparent(name, child) AS (
        SELECT name,child FROM ismother
        UNION ALL
        SELECT name,child FROM isfather
)
SELECT * FROM isparent
ORDER BY 1,2;
The isparent result set is returned:
isparent
name          child
------        ------
Bro           Nephew
Dad           Bro
Dad           Me
Dad           Sis
Dad           StSis
GrDad         Aunt
GrDad         Dad
GrDad         Uncle1
GrDad         Uncle2
GrGrDad       GrDad
GrGrMa        GrDad
GrMa          Aunt
GrMa          Dad
GrMa          Uncle1
GrMa          Uncle2
Me            Daugtr
Me            Son
Mom           Bro
Mom           Me
Mom           Sis
Sis           Niece
Son           GrDaugtr
Son           GrSon
StMom         StSis
Uncle1        Cousin
Because the dependencies are simple, the same result could be achieved with simple substitution into derived tables as shown here:
SELECT * FROM
        ( SELECT name,child FROM
                ( SELECT parent AS name,r.name AS child
                  FROM person p JOIN relatives r
                        ON p.name=r.parent AND gender='F'
                ) AS ismother
          UNION ALL
          SELECT name,child FROM
                ( SELECT parent AS name,r.name AS child
                  FROM person p JOIN relatives r
                        ON p.name=r.parent AND gender='M'
                ) AS isfather
        ) AS isparent
ORDER BY 1,2;