Unexpected results after joining another table

I use three tables to get to the final result. They are called project_board_members, users and project_team.

This is the query:

SELECT `project_board_members`.`member_id`,
       `users`.`name`,
       `users`.`surname`,
       `users`.`country`,
       `project_team`.`tasks_completed`
FROM `project_board_members`
JOIN `users`
    ON (`users`.`id` = `project_board_members`.`member_id`)
JOIN `project_team`
    ON (`project_team`.`user_id` = `project_board_members`.`member_id`)
WHERE `project_board_members`.`project_id` = '5'

You can ignore last line because it just points to the project I'm using.

Ta开发者_Python百科ble project_board_members holds three entries and have structure like:

• id,
• member_id,
• project_id,
• created_at;

I need to get member_id from that table. Then I join to users table to get name, surname and country. No problems. All works! :)

After that, I needed to get tasks_completed for each user. That is stored in project_team table. The big unexpected thing is that I got four entries returned and the big what-the-f*ck is that in the project_board_members table are only three entries.

Why is that so? Thanks in advice!

A SQL join creates a result set that contains one row for each combination of the left and right tables that matches the join conditions. Without seeing the data or a little more information it's hard to say what exactly is wrong from what you expect, but I'm guessing it's one of the following:

1) You have two entries in project_team with the same user_id.

2) Your entries in project_team store both user_id and project_id and you need to be joining on both of them rather than just user_id.

The table project_board_members represent what is called in the Entity-Relationship modelling world an "associative entity". It exists to implement a many-to-many relationship (in this case, between the project and user entities. As such it is a dependent entity, which is to say that the existence of an instance of it is predicated on the existence of an instance of each of the entities to which it refers (a user and a project).

As a result, the columnns comprising the foreign keys relating to those entities (member_id and project_id) must be form part or all of the primary key.

Normally, instances of an associative entity are unique WRT the entities to which it relates. In your case the relationship definitions would be:

• Each user is seated on the board of 0-to-many projects;
• Each project's board is comprise of 0-to-many users

which is to say that a particular user may not be on the board of a particular project more than once. The only reason for adding other columns (such as your id column) to the primary key would be if the user:project relationship is non-unique.

To enforce this rule -- a user may sit on the board a particular project just once -- the table schema should look like this:

create table project_board_member
(
  member_id  int not null foreign key references user    ( user_id ) ,
  project_Id int not null foreign key references project ( project_id ) ,
  created_at ...

  ...

  primary key ( member_id , project_id ) ,
)
}

The id column is superfluous.

For debugging purposes do

SELECT GROUP_CONCAT(pbm.member_id) AS member_ids,
       GROUP_CONCAT(u.name) as names,
       GROUP_CONCAT(u.surname) as surnames,
       GROUP_CONCAT(u.country) as countries,
       GROUP_CONCAT(pt.tasks_completed) as tasks
FROM project_board_members pbm
JOIN users u
    ON (u.id = pbm.member_id)
JOIN project_team pt
    ON (pt.user_id = pbm.member_id)
WHERE pbm.project_id = '5'
GROUP BY pbm.member_id

All the fields that list multiple entries in the result are messing up the rowcount in your resultset.

To Fix that you can do:

SELECT pbm.member_id
       u.name,
       u.surname,
       u.country,
       pt.tasks_completed
FROM (SELECT 
        p.project_id, p.member_id 
        FROM project_board_members p 
        WHERE p.project_id = '5'
      LIMIT 1
     ) AS pbm
JOIN users u
    ON (u.id = pbm.member_id)
JOIN project_team pt
    ON (pt.user_id = pbm.member_id)