Self Joins Part 1

I have a database which contains information on various regions of the world.

For my purposes, the world is divided into kingdoms, and then further subdivided into principalities. Some of those principalities have baronies or shires, which in some cases are further subdivided into cantons.

The structure of my main table looks like this:

Region:
Field1: RegionID int identity primary key
Field2: RegionName varchar(50)
Field3: ParentRegionID int

That last field, ParentRegionID, is a foreign key into the Region table - that is, the Region table is linked to itself.

There are many real life applications for this type of structure. Manufactured products may be made of many components which in turn are made of further subcomponents. A project consists of many tasks which can be divided into sub-tasks.

Suppose I have the following regions listed in my table:

To find all the sub-regions within a given region, I can use a query such as the following:

SELECT RegionName FROM Region
WHERE ParentRegionID=1

This query will return the names of all of the principalities within the Kingdom of the West.

OK, What if I want to know not only the Principalities, but also the next level down?

The following query will return a list of all of the Kingdoms, their principalities, and their baronies:

SELECT 0 AS Depth, RegionName FROM Region
WHERE ParentRegionID is Null
UNION ALL
SELECT 1 AS Depth, t1.RegionName FROM Region t0
INNER JOIN Region t1 ON t0.RegionID=t1.ParentRegionID
WHERE t0.ParentRegionID is Null
UNION ALL
SELECT 2 AS Depth, t2.RegionName FROM (Region t0
INNER JOIN Region t1 ON t0.RegionID=t1.ParentRegionID)
INNER JOIN Region t2 ON t1.RegionID=t2.ParentRegionID
WHERE t0.ParentRegionID is Null

Usually, there is no need to be any cleverer than that. In a lot of cases, it is better to initially only show the user the top one or two levels of detail, and then to wait for them to ask for more detailed data before fetching it.

That's how Windows Explorer works. The treeview control which displays the folder list isn't populated with every single folder on your hard drive when you first open Explorer. Instead, only top level folders are initially visible, and then information about subfolders is fetched when you expand a node.

For those exceptional circumstances when one wants more detail than would be returned by the query above, consider the following stored procedure, written for SQL Server:

CREATE PROCEDURE GetNLevels(@Depth int) AS
BEGIN

DECLARE @sSql varchar(900)
DECLARE @sJoin varchar(900)
DECLARE @iLoop int
SELECT @sSql='SELECT 0 AS Depth, RegionName FROM Region WHERE ParentRegionID is Null'
SELECT @sJoin='Region t0'
SELECT @iLoop=1
WHILE @iLoop<=@Depth
BEGIN
SELECT @sJoin=@sJoin+' INNER JOIN Region t'+Convert(varchar,@iLoop)
+ ' ON t'+Convert(varchar,@iLoop-1)+'.RegionID=t'+Convert(varchar,@iLoop)
+ '.ParentRegionID'
SELECT @sSql=@sSql+' UNION ALL SELECT '+Convert(varchar,@iLoop)+' AS Depth,'
+ 't'+Convert(varchar,@iLoop)+'.RegionName FROM '+@sJoin
+ ' WHERE t0.ParentRegionID is Null'
SELECT @sJoin='('+@sJoin+')'
SELECT @iLoop=@iLoop+1
END

PRINT @sSql
Execute (@sSql)

END

This stored procedure has its limitations. Not the least of them being that if one asks for a great enough depth, eventually, the generated SQL is going to be longer than the 900 characters I have allowed for.

OK, so how would I know what depth to ask for in order to return ALL of the regions in my database?

I honestly cannot think of a way, except

(1) Keep querying at deeper and deeper depths until increasing the depth no longer increases the number of records returned.

OR

(2) Add a "Depth" field to the region table, and write code to maintain the field (eg, using triggers).

The update trigger would check whether the ParentRegionID is being updated, and if it is then get the Depth of the parent region, add 1, and store the result in the Depth field of the region being updated.

NB:  With a Depth field, the depth of the deepest item can be returned using

SELECT Max(Depth) FROM Region

Adelle.