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 theLocationsTable
- Get the
Parent Location
of eachLocation
, continue recursively until there are no more parents - Store this cached data in a view, so that it can be easily
LEFT JOIN
ed on any other table that references aLocID
I hope that all makes sense. Thanks anyone that can help me.