List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:December 24 2005 3:42pm
Subject:Re: MySQL query question
View as plain text  
Josh,

 >I have several tables, all with many-to-many joining tables.
 > users
 > users_teams
 > teams
 > teams_projects
 > projects

Once again explicit join syntax clarifies matters:

  SELECT DISTINCT username
  FROM users
  INNER JOIN users_teams ON (users.id = users_teams.user_id)
  INNER JOIN teams ON (...you didn't mention these keys...)
  INNER JOIN projects_teams ON (teams.id = projects_teams.team_id)
  INNER JOIN projects ON (projects_teams.project_id = projects.id)
  WHERE projects.id = 1;
 
PB

-----

Josh Mellicker 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.
>


-- 
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.371 / Virus Database: 267.14.7/214 - Release Date: 12/23/2005

Thread
MySQL query questionJosh Mellicker24 Dec
  • Re: MySQL query questionHank24 Dec
  • Re: MySQL query questionPeter Brawley24 Dec