Oops, I meant to copy the mailing list on this reply.
Rhino
----- Original Message -----
From: "Rhino" <[EMAIL PROTECTED]>
To: "Josh Mellicker" <[EMAIL PROTECTED]>
Sent: Saturday, December 24, 2005 9:24 AM
Subject: Re: MySQL query question
----- Original Message -----
From: "Josh Mellicker" <[EMAIL PROTECTED]>
To: <mysql@lists.mysql.com>
Sent: Saturday, December 24, 2005 5:23 AM
Subject: MySQL query question
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.
I'm a bit confused by the wording of your question, especially the first
sentence: it seems to be implying that you have actually implemented
direct many-to-many relationships in your database. That is a very rare
thing if you've done it. Normally, each many-to-many relationship is
broken down into two one-to-many relationships with another table, called
an association table or intersection table, between them.
Have a look at this item - http://lists.mysql.com/mysql/176918 - from the
archives where I explained how this works several months ago. After my
remarks about splitting names into first and last name columns, you'll
find a discussion of how a many-to-many relationship is normally
implemented in a relational database.
Now, having said that, your table names suggest that you are already aware
of the normal practice of creating association tables and simply described
it strangely. That is why I'm confused: I don't know what you've actually
done. This is compounded by the fact that you haven't supplied layouts of
the table. I find that knowing the names, primary keys and foreign keys of
each table and the full definition of each column in each table help a
great deal in verifying that the table is correctly designed. I also find
it very useful to see a few sample rows of each table so that I can
visualize the data better. But you haven't done any of that so I have to
operate blind.
I'm going to guess that your remark about "many-to-many joining tables"
simply means association tables and that you simply forgot the correct
terms.
I'm also going to assume that you've designed your tables correctly. I'm
having trouble visualizing it properly 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:
Users (PK=Userid)
===
Userid LastName FirstName
------- ------------ ------------
1 Jones Fred
2 Smith Mary
Teams (PK=TeamName)
====
TeamName TeamCity
------------- -----------
Orioles Baltimore
Cardinals St. Louis
Projects (PK=ProjectNo)
=====
ProjectNo ProjectDescription
---------- ----------------------
A Build new stadium
B Raise money for charity
Users_Teams (PK=TeamName, Userid) (FKs:
User_Teams.TeamName->Teams.TeamName; UserTeams.Userid->Users.Userid)
=========
TeamName Userid
------------- -------
Orioles 2
Cardinals 1
Teams_Projects (PK=TeamName, ProjectNo) (FKs:
Teams_Projects.TeamName->Teams.TeamName;
Teams_Projects.ProjectNo->Projects.ProjectNo)
=============================
TeamName ProjectNo
------------- -----------
Cardinals A
Orioles B
Orioles A
Now, if you want to join all of these five tables together you will need
_at least_ FOUR different joining conditions if you want to avoid getting
duplicate or inappropriate rows. Remember, whenever you join N different
tables together, you always need at least (N-1) different joining
conditions. This is probably why your query isn't working (assuming I am
even somewhat close to how your data is structured): you only have THREE
joining conditions.
With this data, I would do the joins as follows: Users<->Users_Teams;
Users_Teams<->Teams; Projects<->Teams_Projects; Teams_Projects<->Teams
The query would end up looking something like this:
SELECT distinct u.LastName
FROM users u JOIN users_teams ut on u.Userid = ut.Userid
JOIN teams t on ut.TeamName = t.TeamName
JOIN teams_projects tp on t.TeamName = tp.TeamName
JOIN projects p on tp.ProjectNo = p.ProjectNo
WHERE p.ProjectNo = 'A';
or, if you absolutely insist on the old-style syntax, like this:
SELECT distinct u.LastName
FROM users u, users_teams ut, teams t, teams_projects tp, projects p
WHERE p.ProjectNo = 'A'
AND u.Userid = ut.Userid
AND ut.TeamName = t.TeamName
AND t.TeamName = tp.TeamName
and tp.ProjectNo = p.ProjectNo;
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.371 / Virus Database: 267.14.7/214 - Release Date: 23/12/2005
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
