List:General Discussion« Previous MessageNext Message »
From:Hank Date:December 24 2005 3:37pm
Subject:Re: MySQL query question
View as plain text  
>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
Thread
MySQL query questionJosh Mellicker24 Dec
  • Re: MySQL query questionHank24 Dec
  • Re: MySQL query questionPeter Brawley24 Dec