MySQL JOIN for Four Tables Yielding Unexpected Results

Databases
1

I have the following four tables. The query below is supposed to retrieve all projects and related data in associated tables where the project has been archived (i.e., archive field in project_info table set to ‘1’) and user ID = ‘599zxj’.

While user ‘599zxj’ is assigned to 3 projects that have been archived, only one record is returned and it is the project that has a record in the archive_expiration table for user ID = ‘599zxj’.

SELECT `t1`.`id`, `t1`.`name`, `t3`.`status`, `t5`.`access_start_date` 
FROM `project_info` as `t1` 
LEFT JOIN `user_assignments` as `t2` ON `t1`.`id` = `t2`.`propid` 
JOIN `status` AS `t3` ON `t1`.`status` = `t3`.`id` 
LEFT JOIN `archive_expiration` AS `t5` ON `t1`.`id` = `t5`.`propid` 
WHERE `t5`.`userid` = '599zxj' AND `t1`.`archived` = '1' 
GROUP BY `t5`.`propid` 
ORDER BY `t1`.`name`

project_info table - archived field will be ‘1’ when archived

-----------------------------------
id | name    | status | archived 
-----------------------------------
 1 | Proj A  |  4     |  1
 2 | Proj B  |  5     |  1
 3 | Proj C  |  2     |  1
 4 | Proj D  |  3     |  0
 5 | Proj E  |  1     |  0

status table - Provides project status referenced in project_info table

---------------------------
id | status    | sortorder
---------------------------
 1 | prelim    |  1
 2 | stage 1   |  2
 3 | stage 2   |  3
 4 | stage 3   |  4
 5 | submitted |  5

user_assignments table - Associates a user id with a project id to determine project access

------------------------
id | userid    | propid 
------------------------
 1 | 599zxj    |  1
 2 | 599zxj    |  2
 3 | zrt321    |  2
 4 | 599zxj    |  3
 5 | 765xzg    |  3

archive_expiration table - Applies only to archived projects; access can expire for users with records in this table

    ---------------------------------------------
    id | userid  | propid | access_start_date
    ---------------------------------------------
     1 | zrt321  |  2     | 2014-01-15 09:22:56
     2 | 599zxj  |  3     | 2014-09-08 15:45:14
2

Because you put a WHERE condition on a table t5 column, the left join will behave like it was a inner join.
Put that condition in the ON clause instead.

And why do you GROUP BY t5.propid ?

3

dear zosokat, please don’t use those awful horrendous backticks

SELECT t1.id , t1.name , t3.status , t5.access_start_date FROM project_info as t1 INNER JOIN status AS t3 ON t3.id = t1.status LEFT OUTER JOIN archive_expiration AS t5 ON t5.propid = t1.id AND t5.userid = '599zxj' WHERE t1.archived = '1' ORDER BY t1.namenotice that i removed the t2 table from your query, because it wasn’t used anywhere… guido, t2 was probably the reason for the GROUP BY

4

Thank you for the response and the recommendations!

5

Yeah t2 was used in my original query before I posted but I wanted to simplify the query when I posted on this forum. Thanks for your comments and recommendations.