Understanding DB2®: Learning Visually with Examples

9.5. Recursive SQL

Recursive SQL is a powerful way to query hierarchies of data. Organizational structures, bills-of-material, product classifications, and document hierarchies are all examples of hierarchical data. Let's use an example to demonstrate how a recursive SQL statement is written.

Assume that there is a table called children with definition and date as shown in Figure 9.27.

Figure 9.27. Sample data in the children table

CREATE TABLE children ( person_id INTEGER , name VARCHAR(50) , age INTEGER , gender CHAR(1) , parent_id INTEGER ) PERSON_ID NAME AGE GENDER PARENT_ID ----------- -------- ----------- ----- ----------- 1 Apple 10 F 10 2 Zoe 11 F 3 3 John 30 M 13 4 Mary 25 F 24 5 Peter 14 M 4 6 Jenny 13 F 4 24 Robert 60 M 30 7 record(s) ...