Sql-server – Recursive Parent-Child Location Relationship Cache as View

cterecursivesql serverview

Here is my data structure:

CREATE TABLE Locations(
    LocID int IDENTITY(1,1) NOT NULL,
    LocationTypeID int NOT NULL,
    ParentID int NULL,
    LocNum varchar(50) NULL,
    LocName varchar(250) NULL,
)

CREATE TABLE LocationTypes(
    LocationTypeID int IDENTITY(1,1) NOT NULL,
    TypeName varchar(50) NOT NULL
)

The LocationTypes table contains the following values:

+----------------+----------+
| LocationTypeID | TypeName |
+----------------+----------+
|              1 | Campus   |
|              2 | Building |
|              3 | Area     |
|              4 | Floor    |
|              5 | Room     |
+----------------+----------+

The Locations table contains a bunch of locations, some where the ParentID is null (Campuses), and others that have a ParentID. There are 5 levels of hierarchy to my data structure, indicated in the LocationTypes table. Campus (1) being the least specific, and Room(5) being the most specific. In my database I have other tables which reference the LocID, most often these tables are referencing Locations with a LocationTypeID of 5, but not always.

I have a need to get the names of every Location in the hierarchy for display to the end-user.

Ultimately what I want to get is a view (or cached table) with the following structure:

+-------+---------------+-----------------+-------------+--------------+-------------+
| LocID | CampusLocName | BuildingLocName | AreaLocName | FloorLocName | RoomLocName |
+-------+---------------+-----------------+-------------+--------------+-------------+

I realize that some of these columns will end up being null, for Locations with a LocationTypeID of anything other than 5, and that is perfectly fine.

I know that I probably need a recursive common table expression, I just don't really know where to start.

Basically the pseudo-code logic for this is:

Foreach Location in the LocationsTable
Get the Parent Location of each Location, continue recursively until there are no more parents
Store this cached data in a view, so that it can be easily LEFT JOINed on any other table that references a LocID

I hope that all makes sense. Thanks anyone that can help me.

Best Answer

I believe you can achieve the desired result with this query:

;WITH CTE_Locations (LocID, CampusLocName, BuildingLocName, AreaLocName, FloorLocName, RoomLocName)
AS
(
    SELECT LocID, 
        LocName AS CampusLocName, 
        CONVERT(varchar(250), NULL) AS BuildingLocName, 
        CONVERT(varchar(250), NULL) AS AreaLocName, 
        CONVERT(varchar(250), NULL) AS FloorLocName, 
        CONVERT(varchar(250), NULL) AS RoomLocName
    FROM Locations
    WHERE ParentID IS NULL

    UNION ALL

    SELECT L.LocID, 
        CampusLocName, 
        IIF(L.LocationTypeID = 2, LocName, BuildingLocName) AS BuildingLocName, 
        IIF(L.LocationTypeID = 3, LocName, AreaLocName) AS AreaLocName, 
        IIF(L.LocationTypeID = 4, LocName, FloorLocName) AS FloorLocName, 
        IIF(L.LocationTypeID = 5, LocName, RoomLocName) AS RoomLocName
    FROM Locations L
        INNER JOIN CTE_Locations CTE ON L.ParentID = CTE.LocID 
)
SELECT LocID, CampusLocName, BuildingLocName, AreaLocName, FloorLocName, RoomLocName
FROM CTE_Locations;

According to the WITH common_table_expression doc:

This clause can also be used in a CREATE VIEW statement as part of its defining SELECT statement.

Related Solutions

Sql-server – Indexing – Uniqueidentifier Foreign Key or Intermediary mapping table

Ok, I am making a lot of assumptions (INT instead of VARCHAR(50) being one of them) with this answer, so feel free to correct me if needed. The problem with option B is that it introduces a new join to relate Users to Alerts without any real added benefit. If joining on the UserID, it is best to index the UserID, so you can utilize seeks for your joins.

For Option A, UserID will be the clustering key (index key for the clustered index) on the Users table. UserID will be a nonclustered index key on Alerts table. This will cost 16 bytes per Alert.

For Option B, UserID will be the clustering key on the Users table. UserId will probably be the clustering key in UserMap too, to make joining more efficient. UserKey (assuming this is an INT) would then be a nonclustered index key on the Alerts table. This will cost 4 bytes per Alert. And 20 bytes per UserMap.

Looking at the big picture, one relationship, for Option A, costs 16 bytes of storage, and involves 1 join operation. Whereas, one relationship, for Option B, costs 24 bytes of storage, and involves 2 join operations.

Furthermore, there are a possibility of 340,282,366,920,938,000,000,000,000,000,000,000,000 uniqueidentifiers and only 4,294,967,296 INTs. Implementing a uniqueidentifier to INT map for a this type of relationship could cause unexpected results when you start reusing INTs.

The only reason for creating this type map table, is if you plan on creating a Many to Many relationship between Users and Alerts.

Taking all of this into consideration, I would recommend Option A.

I hope this helps,

Matt

Oracle: How to query a Hierarchical table

FrusteratedWithFormsDesigner has the right direction(+1). Here is what I think you are looking for specifically.

CREATE OR REPLACE VIEW BuildingSubs AS
   SELECT connect_by_root location "Building", location "SubLocation"
   FROM some_table l
   START WITH l.Location IN 
      (
         SELECT location FROM
         (
         SELECT level MyLevel, location FROM some_table 
         START WITH parent IS NULL 
         CONNECT BY PRIOR location=parent
         )
         WHERE MyLevel=3   
      )
   CONNECT BY PRIOR l.location = l.parent;

select * from BuildingSubs; 

Building             SubLocation        
-------------------- --------------------
BLDG-01              BLDG-01              
BLDG-01              Basement             
BLDG-01              Room B-002           
BLDG-01              Floor-01             
BLDG-01              Room 1-001           
BLDG-02              BLDG-02              
BLDG-03              BLDG-03              
Grounds              Grounds

The view accomplishes all three goals. You can query it for a building to find everything it contains and you can query it for a sub-location to find what building it is in.

drop table some_table;
create table some_table (Location Varchar2(20), Parent Varchar2(20));

insert into some_table values ('Org. Name',NULL);
insert into some_table values ('MAINCAMPUS','Org. Name');
insert into some_table values ('BLDG-01','MAINCAMPUS');
insert into some_table values ('BLDG-02','MAINCAMPUS');
insert into some_table values ('BLDG-03','MAINCAMPUS');
insert into some_table values ('Grounds','MAINCAMPUS');
insert into some_table values ('Floor-01','BLDG-01');
insert into some_table values ('Basement','BLDG-01');
insert into some_table values ('Room B-002','Basement');
insert into some_table values ('Room 1-001','Floor-01');

If you don't want to count the building itself as one of the sub-locations you can wrap the existing query in one the eliminates entries in which the building and sublocation are the same.

Best Answer

Related Solutions

Sql-server – Indexing – Uniqueidentifier Foreign Key or Intermediary mapping table

Oracle: How to query a Hierarchical table

Related Question