Chapter 13. Hierarchical Queries


This chapter introduces recipes for expressing hierarchical relationships that you may have in your data. It is typical when working with hierarchical data to have more difficulty retrieving and displaying the data (as a hierarchy) than storing it. This is particularly true because of the inflexibility of SQL (SQL's nonrecursive nature). When working with hierarchical queries, it is absolutely crucial that you take advantage of what your RDBMS supplies you to facilitate these operations; otherwise you will end up writing potentially less efficient queries and constructing convoluted data models to deal with the hierarchical data. For PostgreSQL users, the recursive WITH clause will most likely be added to later versions PostgreSQL, so it would behoove you to pay attention to the DB2 solutions to these queries.

This chapter will provide recipes to help you unravel the hierarchical structure of your data by taking advantage of the functions supplied by each of the RDBMSs. Before starting, examine table EMP and the hierarchical relationship between EMPNO and MGR:

  select empno,mgr   from emp order by 2      EMPNO        MGR ---------- ----------       7788       7566       7902       7566       7499       7698       7521       7698       7900       7698       7844       7698       7654       7698       7934       7782       7876       7788       7566       7839       7782       7839       7698       7839       7369       7902       7839 

If you look carefully, you will see that each value for MGR is also an EMPNO, meaning the manager of each employee in table EMP is also an employee in table EMP and not stored somewhere else. The relationship between MGR and EMPNO is a parentchild relationship in that the value for MGR is the most immediate parent for a given EMPNO (it is also possible that the manager for a specific employee can have a manager herself, and those managers can in turn have managers, and so on, creating an n-tier hierarchy). If an employee has no manager, then MGR is NULL.




SQL Cookbook
SQL Cookbook (Cookbooks (OReilly))
ISBN: 0596009763
EAN: 2147483647
Year: 2005
Pages: 235

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net