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.
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
[LIST]
Team11
Team12
Team13
[/LIST]
Office2
[LIST]
Team21
Team22
Team23
[/LIST]
Office3
[LIST]
Team31
Team32
[/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.
Ok, so, I’ve done something like this, with not great results. I’m getting staff appearing where I don’t want them.
SELECT offices.team_name AS office
, teams.team_name AS team
, staff.fullname AS fullname1
, staff2.fullname AS fullname2
FROM hep_teams AS offices
INNER JOIN hep_teams AS teams
ON teams.parent_team = offices.team_id
LEFT JOIN staff
ON staff.team = hep_teams.team_name
LEFT JOIN staff AS staff2
ON staff2.team = hep_teams.team_name
ORDER BY offices.team_name
OK, so… I have a UNION set up, but bear with me, because this is my first UNION with multiple JOINS, so I’m still a bit green.
First off, it looks like both SELECTs have to be pretty much the same SELECT, because otherwise I get an error about an uneven number of columns, but one of the JOINs is slightly different. Second, it looks like the ORDER BY clause has to be on the first SELECT.
SELECT offices.team_name AS office
, teams.team_name AS team
, staff.fullname AS fullname
FROM hep_teams AS offices
INNER JOIN hep_teams AS teams
ON teams.parent_team = offices.team_id
INNER JOIN staff
ON staff.team = offices.team_name
ORDER BY staff.rank, offices.team_name
UNION
SELECT offices.team_name AS office
, teams.team_name AS team
, staff.fullname AS fullname
FROM hep_teams AS offices
INNER JOIN hep_teams AS teams
ON teams.parent_team = offices.team_id
INNER JOIN staff
ON staff.team = teams.team_name
Now I have everyone showing up, and mostly in the proper place, except for the team leaders and assistants (first SELECT), who are showing up under a team, when they’re not listed under a team. When I cfdump the query, sure enough, they have a populated team column, when in fact they’re not a member of any team. I’m not sure what’s doing that.
It’s also ignoring my rank ordering, making the admin assistants appear above the bosses (eek!)
this makes sense when you think about it – both SELECTs have to feed rows into the same result set
the secret is to use “placeholder” columns to make the number equal
nope, a UNION is allowed to have only one ORDER BY and it goes at the very end (i’m suprised you didn’t get a syntax error)
try this –
SELECT offices.team_name AS office
, teams.team_name AS team
, staff.fullname AS fullname
, staff.rank
FROM hep_teams AS offices
INNER
JOIN hep_teams AS teams
ON teams.parent_team = offices.team_id
INNER
JOIN staff
ON staff.team = [COLOR="#0000FF"][B]teams[/B][/COLOR].team_name
UNION [COLOR="#0000FF"][B]ALL[/B][/COLOR]
SELECT offices.team_name AS office
, NULL AS team
, staff.fullname AS fullname
, staff.rank
FROM hep_teams AS offices
INNER
JOIN staff
ON staff.team = [COLOR="#0000FF"][B]offices[/B][/COLOR].team_name
ORDER
BY office
, team
, rank
So, it would seem that my problem stems from the fact that my UNION sees the same rows in two different ways. I have one set (from the first SELECT) with a team name, and another result (from the second SELECT) with null for the team name. The UNION sees these as two different values, so it’s displaying them both, so therefore I’m getting duplicates of all the staff members who are members of a team (but not the bosses).
the query i gave you, if i did it right, pulls staff connected to teams (first select, two joins), and then staff connected to offices (second select, only one join)
please confirm that you understand how my union query works
if you’re still getting dupes, perhaps you actually entered the staff under both offices and teams?
When I run the top half of the query alone, I get everyone who is assigned to a team, and I don’t get any of the bosses who are not members of a team.
When I run the bottom half alone, I get everyone, with a NULL as the team, but the office is there. I think what I need is a way for the second half of the query to show only the people who are not team members to show up, rather than everyone. I can’t use WHERE team IS NULL because that would hide everyone, since I assigned NULL to all the teams.
The staff table goes is like this:
name
phone#
email
location
rank
team (this is what gets JOINED to the teams table).
It’s joined on staff.team = teams.team_name, so there aren’t any duplicate assignments. Everyone is in the staff table only once.
If parent_team is null, that person is a boss. Otherwise they’re a team member. I’ve tried variations on WHERE statements using that, but haven’t had any luck.
i based my query on the query you gave in post #6, in which you incorrectly joined the staff using the team name
try this correction –
SELECT offices.team_name AS office
, teams.team_name AS team
, staff.name
, staff.rank
FROM hep_teams AS offices
INNER
JOIN hep_teams AS teams
ON teams.parent_team = offices.team_id
INNER
JOIN staff
ON staff.team = teams.[COLOR="#ff8000"][B]team_id[/B][/COLOR]
UNION ALL
SELECT offices.team_name AS office
, NULL AS team
, staff.name
, staff.rank
FROM hep_teams AS offices
INNER
JOIN staff
ON staff.team = offices.[COLOR="#ff8000"][B]team_id[/B][/COLOR]
ORDER
BY office
, team
, rank
Well it actually does get joined on the team_name field.
The team field in the staff database is something like OBPM-30. It’s this way because I almost never have to show any of the other info from the teams table. So it’s joined on the text fields staff.team = teams.team_name
So your original query should have been right.
The team_id is only used to identify the parents teams.
What’s happening is this (I’ll use me as an example):
name | office | team
row 5: Paul | My Office Name | My Team Name
row 11: Paul | My Team Name | Null
Row 5 is correct. Row 11 has my team name in the office code column, and the team name column is null.
I went through the query line-by-line, and here’s what I found:
SELECT offices.team_name AS office
, teams.team_name AS team
, staff.name
, staff.rank
FROM hep_teams AS offices
INNER
JOIN hep_teams AS teams
ON teams.parent_team = offices.team_id
INNER
JOIN staff
ON staff.team = teams.team_id
UNION ALL
[COLOR="#ff8000"][B]SELECT offices.team_name AS office
, NULL AS team[/B][/COLOR]
, staff.name
, staff.rank
FROM hep_teams AS offices
INNER
JOIN staff
ON staff.team = offices.team_id
ORDER
BY office
, team
, rank
My team is OBPM-30 and my office is OBPM-1. In the above line, we’re selecting my TEAM NAME as office, so of course I’m there twice!
I made a slight change:
SELECT offices.team_name AS office
, teams.team_name AS team
, staff.name
, staff.rank
FROM hep_teams AS offices
INNER
JOIN hep_teams AS teams
ON teams.parent_team = offices.team_id
INNER
JOIN staff
ON staff.team = teams.team_id
UNION ALL
[COLOR="#ff8000"][B]SELECT NULL AS office
, offices.team_name AS team[/B][/COLOR]
, staff.name
, staff.rank
FROM hep_teams AS offices
INNER
JOIN staff
ON staff.team = offices.team_id
ORDER
BY office
, team
, rank
And this fixed everything!
EDIT: Actually, it fixed everything in the CFDUMP, and for around 10 minutes. I added some more columns to get people’s job titles, and the stupid dupes are back. At least it felt good to think I solved it for 10 minutes.
SELECT offices.team_name AS office
, teams.team_name AS team
, staff.name
, staff.rank
FROM hep_teams AS offices
INNER
JOIN hep_teams AS teams
ON teams.parent_team = offices.team_id
INNER
JOIN staff
ON staff.team = teams.team_id
UNION ALL
SELECT NULL AS office
, offices.team_name AS team
, staff.name
, staff.rank
FROM hep_teams AS offices
INNER
JOIN staff
ON staff.team = offices.team_id
[COLOR="#ff8000"][B]WHERE parent_team IS NULL[/B][/COLOR]
ORDER
BY office
, team
, rank
Dupes are gone. Now I just need to get the order right.
EDIT: Once I ordered it properly (there was a “team_order” column in the teams table) everything works (FINALLY!).
