MySQL ODBC Connector - MySQL query question

Author

MySQL query question

Josh Mellicker

2005-12-24, 7:23 am

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...sie.nctu.edu.tw

Hank

2005-12-24, 11:23 am

>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 =3D '1'
AND pt.project_id =3D p.project_id
AND ut.team_id =3D pt.team_id
AND u.user_id =3D 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@dvcreators.net> wrote:
> I have several tables, all with many-to-many joining tables.
>
> users
>
> users_teams
>
> teams
>
> teams_projects
>
> projects
>
>
> ---
>
> So, with a projects.id =3D 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 =3D '1'
> AND projects_teams.project_id =3D projects.id
> AND teams.id =3D projects_teams.team_id
> AND users_teams.user_id =3D 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...mail
.com
>
>

--

-Hank

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Peter Brawley

2005-12-24, 11:23 am

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

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Sponsored Links