>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 <josh@stripped> 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/mysql?unsub=1>
>
--
-Hank

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.