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;