Retrieving hierarchy at all levels - Self joining table

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
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.



yes, you can do this with one query, but only if there is a maximum number of levels that you can go “down” from the top level to the lowest

if so, the query just uses as many self-joins as levels


Thanks, any ideas if the number of levels is dynamic?

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… :confused:

no, you’re not overanalyzing it, you’ve almost got it :slight_smile:

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 :slight_smile:

  • 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 :slight_smile:

P.S. If you can think of a better way of doing this…I am open to other suggestions.

my approach would be to store the actual headings to use for each league too, and use them to create my TH cells in my html TABLE

but even if you had to hardcode the headings for each league, this is still the right way to store the data and the fastest query to return the data