MySQL Forums
Forum List  »  Newbie

Left Join With Nulls and Max
Posted by: James Medick
Date: January 02, 2020 04:50PM

I have two tables. The first has tasks, the second has task reports. There are multiple task reports for a task arranged by date. I'm trying to get a list of all task (including those that have no task reports at all) with the respective latest task report for that task. I succeed in getting the correct reporting tasks for each task but am missing the null values for any task that does not have at least one report. Tables Look like this:

Task Task Report
Task One report 12/22
Task One report 12/20
task two

SQL: looks like this:

SELECT
project_tasks.idproject_tasks,
project_tasks.project_task,
project_task_report.idproject_tasks,
project_task_report.date_submitted
From project_tasks
Left Join project_task_report on project_tasks.idproject_tasks = project_task_report.idproject_tasks
Where project_task_report.date_submitted In (Select Max(project_task_report.date_submitted) From project_task_report
Group By project_task_report.idproject_tasks)

Again, everything is fine except for the omitted tasks that have no task report at all (I don't get a row for task two).

James

Options: ReplyQuote


Subject
Written By
Posted
Left Join With Nulls and Max
January 02, 2020 04:50PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.