Reputation: 327
So I have been tasked with creating a SQL Server stored procedure to assemble the geography hierarchy for an employee.
The system has 3 types of geography:
In the database the geographies table looks similar to this:
GeographyID | GeographyType | GeographyName | ParentGeographyID |
-----------------------------------------------------------------
1 National Nation NULL
2 Region South 1
3 Territory Florida 2
There is also and employee table. A single employee can be assigned to any of the geographies above.
For example if employee 105 was assigned to the "South" Region there is an entry in an XREF table like so:
EmployeeID | GeographyID
------------------------
105 2
What I need to do is given an employee ID, build their geography hierarchy. So the result for employee 105 would look something like this:
EmployeeID | TerritoryGeographyID | RegionGeographyID | NationalGeographyID
---------------------------------------------------------------------------
105 NULL 2 1
I'm at a loss of how to set up such a data structure. I'm hoping someone may have some insight on this problem.
Upvotes: 2
Views: 689
Reputation: 3684
Given that the expected result should show all the level, and the level are few there are solution that don't use recursive CTE
, for example
WITH G AS (
SELECT g.GeographyID
, t.GeographyID TerritoryGeographyID
, r.GeographyID RegionGeographyID
, n.GeographyID NationalGeographyID
FROM Geography g
LEFT JOIN Geography T ON (g.GeographyID = T.GeographyID)
AND (T.GeographyType = 'Territory')
LEFT JOIN Geography R ON ((g.GeographyID = R.GeographyID)
OR (R.GeographyID = T.ParentGeographyID))
AND (R.GeographyType = 'Region')
LEFT JOIN Geography N ON ((g.GeographyID = N.GeographyID)
OR (N.GeographyID = R.ParentGeographyID))
AND (N.GeographyType = 'National')
)
SELECT E.EmployeeID
, TerritoryGeographyID
, RegionGeographyID
, NationalGeographyID
FROM Employee E
INNER JOIN G ON E.GeographyID = G.GeographyID;
In the CTE
the Geography
is partitioned and reassembled from vertical to horizontal, the main query JOIN
the 'PIVOT
ed' data to the employee data, the ID can be changed to the location name by changing [trn].GeographyID
to [trn].GeographyName
The partitioning can be explicited defining a CTE
for every level
With N AS (
SELECT GeographyID, GeographyName
FROM Geography
WHERE GeographyType = 'National'
), R AS (
SELECT GeographyID, GeographyName, ParentGeographyID
FROM Geography
WHERE GeographyType = 'Region'
), T AS (
SELECT GeographyID, GeographyName, ParentGeographyID
FROM Geography
WHERE GeographyType = 'Territory'
), G AS (
SELECT g.GeographyID
, t.GeographyID TerritoryGeographyID
, r.GeographyID RegionGeographyID
, n.GeographyID NationalGeographyID
FROM Geography g
LEFT JOIN Geography T ON (g.GeographyID = T.GeographyID)
LEFT JOIN Geography R ON (g.GeographyID = R.GeographyID)
OR (R.GeographyID = T.ParentGeographyID)
LEFT JOIN Geography N ON (g.GeographyID = N.GeographyID)
OR (N.GeographyID = R.ParentGeographyID)
)
SELECT E.EmployeeID
, TerritoryGeographyID
, RegionGeographyID
, NationalGeographyID
FROM Employee E
INNER JOIN G ON E.GeographyID = G.GeographyID;
Upvotes: 3