Fw: MySQL query question

[Rhino]

Oops, I meant to copy the mailing list on this reply.

Rhino

----- Original Message ----- 
From: "Rhino" <rhino1@stripped>
To: "Josh Mellicker" <josh@stripped>
Sent: Saturday, December 24, 2005 9:24 AM
Subject: Re: MySQL query question


>
> ----- Original Message ----- 
> From: "Josh Mellicker" <josh@stripped>
> To: <mysql@stripped>
> 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