SQL for Self-Referencing Tables

Glenn Andrews

SQL for Self-Referencing Tables
With all of the enhancements to SQL, is there any convenient and
well-performing SQL to process self-referencing tables? For example, the
DB2 Samples include the DEPT table:

DEPTNO CHAR(3)
DEPTNAME VARCHAR(36)
MGRNO CHAR(6)
ADMRDEPT CHAR(3)
LOCATION CHAR(16)

With one SQL query, I would like to return all subordinate departments for
any given department. The use of multiple subqueries can do this if the
number of levels is constant. I would like to know if there one SQL
statement which will work without knowing the number of levels.

Thanks for your help,

Glenn Andrews



Edward Vassie

Re: SQL for Self-Referencing Tables
(in response to Glenn Andrews)
DB2 for AIX, NT, etc has recursive queries.

For a good discussion of their strengths and weaknesses, look at the SQL
Cookbook, available for free from
http://ourworld.compuserve.com/homepages/Graeme_Birchall/HTM_COOK.HTM

From Edward Vassie...


-----Original Message-----
From: Glenn Andrews [mailto:[login to unmask email]
Sent: 10 January 2001 15:15
To: [login to unmask email]
Subject: SQL for Self-Referencing Tables


With all of the enhancements to SQL, is there any convenient and
well-performing SQL to process self-referencing tables? For example, the
DB2 Samples include the DEPT table:

DEPTNO CHAR(3)
DEPTNAME VARCHAR(36)
MGRNO CHAR(6)
ADMRDEPT CHAR(3)
LOCATION CHAR(16)

With one SQL query, I would like to return all subordinate departments for
any given department. The use of multiple subqueries can do this if the
number of levels is constant. I would like to know if there one SQL
statement which will work without knowing the number of levels.

Thanks for your help,

Glenn Andrews