I'm currently recreating a table in my database that has a category/subcategory relationship. The categories are offices, and within each office is one or more team (the "subcategories"). Each team contains lots of people, and the page I'm building is basically an office directory.

Currently I've structured the table like this:

team_id (numeric)
team_name (text)
team_desc (text)
parent_team (numeric)

I have the following query:

Code SQL:
SELECT    offices.team_name AS office
        , teams.team_name AS team
FROM hep_teams AS offices
INNER JOIN hep_teams AS teams
        ON teams.parent_team = offices.team_id
ORDER BY offices.team_name

Which gives me a nice bulleted list:

  • Office1
    • Team11
    • Team12
    • Team13

  • Office2
    • Team21
    • Team22
    • Team23

  • Office3
    • Team31
    • Team32

Now, my problem comes when I have to join my staff list. Each staff member is a member of one of these teams. But when I join my staff table in, I'm not getting all of my people. It appears that I have to join my staff table twice - once to the teams table on its "office" alias, and again to the teams table as its "teams" alias. This is quite clunky.

I think my stumbling block is that everyone in the office is a member of a team EXCEPT the big boss and her two assistants, who are members of Office 1 but not any team. I think this is why I'm ending up having to join the staff table twice. Unless of course, there is a better way that I'm missing.