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

Question

Table 1: project_list

id	name
5	A
6	B
7	C
8	D
9	E

Table 2 : user_productivity

id	project_id	task_id	comment	date_created
1	5	1	p5.task1	2021-09-28 13:40:19
2	6	1	p6.task1	2021-09-28 13:41:19
3	7	1	p7.task1	2021-09-28 13:42:19
4	8	1	p8.task1	2021-09-28 13:43:19
5	5	2	p5.task2	2021-09-28 13:44:19
6	6	2	p6.task2	2021-09-28 13:45:19
7	7	2	p7.task2	2021-09-28 13:46:19
8	8	2	p8.task2	2021-09-28 13:47:19

Table 3 : task_list

id	project_id
1	5
2	6
3	7
4	8
5	9

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

No	id (on table project_list)	name	comment	date_created
1	5	A	p5.task1	2021-09-28 13:40:19
2	6	B	p6.task1	2021-09-28 13:41:19
3	7	C	p7.task1	2021-09-28 13:42:19
4	8	D	p8.task1	2021-09-28 13:43:19
5	9	E

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

No	id (on table project_list)	name	comment	date_created
1	5	A	p5.task2	2021-09-28 13:44:19
2	6	B	p6.task2	2021-09-28 13:45:19
3	7	C	p7.task2	2021-09-28 13:46:19
4	8	D	p8.task2	2021-09-28 13:47:19
5	5	A	p8.task1	2021-09-28 13:43:19
6	6	B	p7.task1	2021-09-28 13:42:19
7	7	C	p6.task1	2021-09-28 13:41:19
8	8	D	p5.task1	2021-09-28 13:40:19
9	9	E

The result is 'comment' is solved but group id show all query

The result I want :

No	id (on table project_list)	name	comment	date_created
1	5	A	p5.task2	2021-09-28 13:44:19
2	6	B	p6.task2	2021-09-28 13:45:19
3	7	C	p7.task2	2021-09-28 13:46:19
4	8	D	p8.task2	2021-09-28 13:47:19
5	9	E

Answer 1

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

Answer 2

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

id	project_id	task_id	comment	date_created
1	5	1	p5.task1	2021-09-28 13:40:19
2	5	2	p6.task2	2021-09-28 13:40:19
3	5	3	p6.task3	2021-09-28 13:38:19

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

ID	project_id
5	5
6	6
7	7
8	8

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