MySQL JOIN for Four Tables Yielding Unexpected Results

[JipThePeople]

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

 

Any recommendations will be greatly appreciated.

[requinix]

You made it a requirement that there be an entry in archive_expiration for the user/project pair with

WHERE `t5`.`userid` = '599zxj'

I'm not even sure why you need that table in the first place? It only seems to be used to limit the data returned, and it limits in a way you don't want.

[Barand]

If that condition is require it needs to be part of the JOIN condition (as it is a left join on t5) and removed from the WHERE clause

...
LEFT JOIN `archive_expiration` AS `t5` ON `t1`.`id` = `t5`.`propid`
 AND `t5`.`userid` = '599zxj'
...

[JipThePeople]

You made it a requirement that there be an entry in archive_expiration for the user/project pair with

WHERE `t5`.`userid` = '599zxj'

I'm not even sure why you need that table in the first place? It only seems to be used to limit the data returned, and it limits in a way you don't want.

 

 

Thanks for the response. The archive_expiration table is required because certain users when added to a project, have their access limited to 180 days, Therefore, an access start time record is inserted into the archive_expiration table and is used when determining project access by comparing the access start date to the current date.

[mikosiko]

Thanks for the response. The archive_expiration table is required because certain users when added to a project, have their access limited to 180 days, Therefore, an access start time record is inserted into the archive_expiration table and is used when determining project access by comparing the access start date to the current date.

 

So given the example data that you posted, which are the expected results (in detail using the posted data)?

 

I will suggest you to test your query  eliminating first the GROUP BY and the "AND `t5`.`userid` = '599zxj'" filter temporarily that will allow you to analyze which are the results of your basic JOINS and proceed from there applying the filters that you consider necessary.

[JipThePeople]

 

If that condition is require it needs to be part of the JOIN condition (as it is a left join on t5) and removed from the WHERE clause

...
LEFT JOIN `archive_expiration` AS `t5` ON `t1`.`id` = `t5`.`propid`
 AND `t5`.`userid` = '599zxj'
...

 

Thanks for the response. So integrating your recommendation, the updated query is now:

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` AND `t5`.`userid` = '599zxj'
WHERE `t1`.`archived` = '1' 
GROUP BY `t5`.`propid` 
ORDER BY `t1`.`name`

User '599zxj' is assigned to 3 archived projects, one of which has a corresponding record in the archive_expiation table, This revised query will retrieve 2 of the 3 records, but if I remove the  corresponding record in the archive_expiation table, it will only retrieve 1 of the 3 records. Any other recommendations are greatly appreciated. 

 

[Barand]

I notice you are grouping by a value that may not exist too. Try GROUP BY t1.id

[JipThePeople]

I notice you are grouping by a value that may not exist too. Try GROUP BY t1.id

 

Thanks so much for the recommendation. The group by t1.id was the solution. Many thanks to you as well as the others who posted a response.

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` AND `t5`.`userid` = '599zxj'
WHERE `t1`.`archived` = '1' 
GROUP BY `t1`.`id` 
ORDER BY `t1`.`name`

[JipThePeople]

To complete the query I had to add the code: AND `t2`.`userid` = '599zxj' to the WHERE statement or it would retrieve archived projects NOT assigned to user '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` AND `t5`.`userid` = '599zxj'
WHERE `t1`.`archived` = '1' AND `t2`.`userid` = '599zxj'
GROUP BY `t1`.`id` 
ORDER BY `t1`.`name`

Edited October 9, 2014 by JipThePeople

[Barand]

Again, as there is a LEFT JOIN to t2 that condition should be in the t2 JOIN ON clause and not the WHERE clause