Results 1 to 19 of 19
Apr 11, 2013, 11:20 #1
- Join Date
- Jan 2006
- Merry Land, USA
- 3 Post(s)
- 0 Thread(s)
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:
I have the following query:
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:
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.<cfset myblog = "http://cydewaze.org/">