>since I'm not sure how users would > ever be directly associated with teams - I would have expected to find > players to be associated with teams - so forgive me if this doesn't > resemble very much what you're doing:
Think corporate projects, not sports. Here's my take on the original query.. you don't actually need to use the "teams" table in the query, as long as you have DISTINCT in the Select: SELECT DISTINCT username FROM users u, users_teams ut, projects_teams pt , projects p WHERE p.project_id = '1' AND pt.project_id = p.project_id AND ut.team_id = pt.team_id AND u.user_id = ut.user_id Also, just a style comment, I would find it confusing just to use "id" as the key in the projects, team, and user tables.. and "user_id", "team_id", and "project_id" in the associative tables... the field names should be consistent throughout, so when reading queries, it's obvious which "id" one is talking about. On 12/24/05, Josh Mellicker <[EMAIL PROTECTED]> wrote: > I have several tables, all with many-to-many joining tables. > > users > > users_teams > > teams > > teams_projects > > projects > > > --- > > So, with a projects.id = 1, I want to get all the usernames of people > on teams assigned to that project. > > SELECT DISTINCT username > FROM users, users_teams, teams, projects_teams, projects > WHERE projects.id = '1' > AND projects_teams.project_id = projects.id > AND teams.id = projects_teams.team_id > AND users_teams.user_id = users.id > > gives me ALL the users who are on any team... even teams not assigned > to that project. > > What gives? My brain hurts. Thanks for any help. > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > -- -Hank -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
