Good morning all. I am creating a hierarchy in Microsoft Access (I know, I know but it is all I have to work with). Since the hierarchy is quite complex and could potentially go quite deep, we decided to go with a self referencing table. Below is the basic table design:
CategoryID - Primary Key
CategoryName - Name of the category
ParentCategoryID - FK to CategoryID
Let me give you an example of what I am trying to do…Let’s take sports for example. Every sport has a league for the most part (MLB, NFL, NBA etc…) but under the league could be a number of different levels including Divisions, Conferences, Teams etc. I know how to get the data in, but I am trying to figure out the best way to pull it out. I would like to go to the deepest level and pull all info for that item. Simple Example:
CategoryID,CategoryName,ParentCategoryID
1, MLB, NULL
2, American League, 1
3, National League, 1
4, Central Division, 3
5, Chicago Cubs, 4
Since the cubs are at the lowest level, I would like 1 Access query to return the league(MLB), Conference(National League), Division(Central Division) and Team(Chicago Cubs). Let me know your thoughts or if you would like further info.
if the number of levels is truly dynamic with no maximum number of levels, or if the number of levels is is dynamic but never more than a certain number?
Yes there is a maximum but inside of that could be dynamic Example:
MLB>National League>Central Division>Chicago Cubs
NHL>Eastern Conference>Tampa Bay
SELECT – I would like to show all of these (With Nulls if necessary)
C3.CategoryID AS LeagueID,
C3.CategoryName AS LeagueName,
C2.CategoryID AS ConferenceID,
C2.CategoryName AS ConferenceName,
C1.CategoryID AS DivisionID,
C1.CategoryName AS DivisionName,
C.CategoryID AS TeamID,
C.CategoryName AS TeamName
FROM Categories AS C
LEFT JOIN Categories AS C1 ON C.ParentCategoryID=C1.CategoryID
LEFT JOIN Categories AS C2 ON C1.ParentCategoryID=C2.CategoryID
LEFT JOIN Categories AS C3 ON C2.ParentCategoryID=C3.CategoryID
where C2.ParentCategoryID IS NOT NULL
In the NHL there is one less level and therefore the query would not work for them the same way it would for the MLB. Maybe I am overanalyzing this…
no, you’re not overanalyzing it, you’ve almost got it
let me just rearrange your joins for you into the correct sequence
typically, you would want to start at the “roots” (top levels) of the trees*, i.e. those which have no parent
select C1.CategoryID as LeagueID
, C1.CategoryName as LeagueName
, C2.CategoryID as ConferenceID
, C2.CategoryName as ConferenceName
, C3.CategoryID as DivisionID
, C3.CategoryName as DivisionName
, C4.CategoryID as TeamID
, C4.CategoryName as TeamName
from Categories as C1
left outer
join Categories as C2
on C1.CategoryID = C2.ParentCategoryID
left outer
join Categories as C3
on C2.CategoryID = C3.ParentCategoryID
left outer
join Categories as C4
on C3.CategoryID = C4.ParentCategoryID
where C1.ParentCategoryID is null
i think you will find the column alias names a bit problematic for any leagues where the team is at the 3rd level
however, they did help me to analyze what you wanted
i have no idea why we draw and think of hierarchies as trees, because the roots are at the top and the leaves are at the bottom – however, the tree analogy is what everybody uses
Now the only issue is that Tampa Bay shows up as a division and the team is null… I need it to show the division as null and the team to be Tampa Bay. Making some progress and I really appreciate your help thus far
P.S. If you can think of a better way of doing this…I am open to other suggestions.
