How to join two tables on mysql or php with multiple condition

Table 1: project_list

Table 2 : user_productivity

Table 3 : task_list

My question is how to join two tables and show query column ‘comment’ is only the last update or DESCENDING from column ‘date_created’.
Please Help To solved my problem with this condition. Thank you very much in advance. Cheers!

SELECT project_list.id, project_list.name, user_productivity.project_id, 
        user_productivity.comment, user_productivity.date_created 
FROM project_list 
    LEFT JOIN user_productivity ON project_list.id = user_productivity.project_id 
GROUP BY project_list.id

The result is : group id is solved. but comment not last updated date_created

I also try :

SELECT project_list.id, project_list.name, user_productivity.project_id, 
        user_productivity.comment, user_productivity.date_created 
FROM project_list 
    LEFT JOIN user_productivity ON project_list.id = user_productivity.project_id 
ORDER BY unix_timestamp(user_productivity.date_created) desc

The result is ‘comment’ is solved but group id show all query

The result I want :

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

This Problem is not really solvable without more input, imagine your input table would user_productivity table would look like

So for project_id 5 which is the last updated date with which corresponding comment?
You would need to either define a unique constraint on project_id and date_created so you wouldn’t be able to have two comments with the same date_created on a project or you would need to say, e.g. I always want to use the date_created and comment which got inserted last and the ID is auto increment.

Instead of joining to your user_productivity table you need to join to a representation of the data that only contains project_id and the last update date

You would get this by

SELECT project_id, MAX(date_created) as date_created FROM user_productivity GROUP BY project_id

This results in
| project_id | date_created |
|————|————–|
|5| 2021-09-28 13:44:19 |
|6| 2021-09-28 13:45:19 |
|7| 2021-09-28 13:46:19 |
|8| 2021-09-28 13:47:19|

But in this case you are missing the comment column from the user_productivity group, while you could just join again to user_productivity on project_id and date_created, and if you have e.g. a unique constraint on project_id and date_created I would go for this, but if not you could have multiple rows that match that project_id and date_created.

If you know that e.g. always the biggest ID in user_productivity is the newest, because of e.g. autoincrement you could also first join to

SELECT MAX(ID) as ID, project_id FROM user_productivity GROUP BY project_id

Which would result in

That you could join now again to user_productivity based on the ID and get all the columns you need.

SELECT project_list.id, project_list.name, user_productivity.project_id, 
    user_productivity.comment, user_productivity.date_created 
FROM project_list
LEFT JOIN (SELECT MAX(ID) as ID, project_id FROM user_productivity GROUP BY project_id) helper 
    ON helper.project_id = project_list.id
LEFT JOIN user_productivity ON user_productivity.id = helper.id 
GROUP BY project_list.id

Method 2

Amazing! Thank you very much for your help.
Yes. need another table to join all connection. i put id from table task_list where task_id on user_productivity table is id on table task_list(see table 3 : task_list my clarification on my question)

SELECT project_list.id, project_list.name, user_productivity.project_id, user_productivity.comment, user_productivity.date_created, project_list.claim_no, project_list.claim_status, project_list.assigned_user
FROM project_list 
LEFT JOIN (SELECT MAX(ID) as ID, project_id FROM user_productivity GROUP BY project_id) task_list ON task_list.project_id = project_list.id 
LEFT JOIN user_productivity ON user_productivity.id = task_list.id GROUP BY project_list.id

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0