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

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.

r937
—
2013-04-11T21:36:24Z —
#2

cydewaze said:

I think this is why I'm ending up having to join the staff table twice.

nailed it right there

cydewaze
—
2013-04-12T03:14:25Z —
#3

r937 said:

nailed it right there

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

What I'm looking for is this:

Office1

Bill Smith - Office Director

Jane Doe - Admin assistant

Team 11

Bob Clark - Team Leader

Sam Spade - Team Member

Joe Jones - Team Member

Team 12

Mike Johnson - Team Leader

Gail Reed - Team Member

Mark Lane - Team Member

Office 2

Jill Hoff - Office Director

Sam Walker - Admin assistant

Team 21

Steve Schneider - Team Leader

Bruce James - Team Member

Silvia Wu - Team Member

Team 22

Pat Cumins - Team Leader

Sue Sheperd - Team Member

Linda Plant - Team Member

And what I keep getting is this:

Office1

Bill Smith - Office Director

Jane Doe - Admin assistant

Bob Clark - Team Leader

Sam Spade - Team Member

Joe Jones - Team Member

Mike Johnson - Team Leader

Gail Reed - Team Member

Mark Lane - Team Member

Team 11

Bill Smith - Office Director

Jane Doe - Admin assistant

Bob Clark - Team Leader

Sam Spade - Team Member

Joe Jones - Team Member

Mike Johnson - Team Leader

Gail Reed - Team Member

Mark Lane - Team Member

Team 12

Bill Smith - Office Director

Jane Doe - Admin assistant

Bob Clark - Team Leader

Sam Spade - Team Member

Joe Jones - Team Member

Mike Johnson - Team Leader

Gail Reed - Team Member

Mark Lane - Team Member

Office 2

[snip]

I'm obviously tripping up somewhere.

r937
—
2013-04-12T06:06:13Z —
#4

i could've warned you about that, i guess

what you need is a UNION

one SELECT for the offices, plus staff if any

another SELECT for the teams, plus staff if any

ORDER BY on the UNION will ensure the rows get interleaved properly

cydewaze
—
2013-04-12T11:38:21Z —
#5

OOOH! I actually had started to try that, but figured that if I thought of it, it couldn't be right, so I stopped, hah!

cydewaze
—
2013-04-12T13:46:39Z —
#6

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.

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.

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)

It was probably some silly Access idiosyncrasy. Putting the ORDER BY at the end gave me this error:

Only those fields requested in the first query can be included in an ORDER BY expression

I'm always having ORDER BY problems. It seems like sometimes I am allowed to order by a column alias, and other times I'm not.

Your new version (of COURSE) works perfectly (once I added the parens), but don't expect me not to inspect it to see where I went wrong with mine!

Now, I just have to show my results in a nice way, because the unordered list method we have now is a bit ugly.

cydewaze
—
2013-04-12T15:13:20Z —
#9

HMMM! I think you may have thrown me a curveball there, but don't you worry, I'll figure it out!

cydewaze
—
2013-04-12T16:58:10Z —
#10

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

Removing the dupes is proving errrr, challenging.

r937
—
2013-04-12T17:26:30Z —
#11

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?

cydewaze
—
2013-04-12T18:07:07Z —
#12

I'm pretty sure I do.

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:

namephone#emaillocationrankteam (this is what gets JOINED to the teams table).

i based my query on the query you gave in post #6, in which you incorrectly joined the staff using the team name

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

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.