----- Original Message ----- From: "Josh Mellicker" <jo...@dvcreators.net> To: <mys...@lists.mysql.com> Sent: Saturday, December 24, 2005 5:23 AM Subject: MySQL query question

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;