SQLite3 Simulate the CORRECT INTERACTING FOLLOW-UP WITH LEFT WAYS AND UNION

Question

I have the following select statement, in which I need to summarize each task from the tbTasks table and group it by projectId from the tbProjects table to get such an entry:

ProjectID = 1, ProjectName = 'My Project', TotalTime = 300 //<--sum of each task time

The request is as follows:

SELECT tbTasks.projectId, 
       SUM(tbTasks.taskTime) AS totalTime, 
       tbProjects.projectName 
FROM tbTasks 
    INNER JOIN tbProjects ON tbTasks.projectId = tbProjects.projectId 
GROUP BY tbTasks.projectId 
ORDER BY tbProjects.created DESC

This works and works fine, but with one problem, if the project has no task associated with it, then I don’t get any record at all (where I want to get projectId, projectName and 0 or NULL for totalTime). So, in order to join the tbProjects table correctly, SQLite3 forces me to do this in a circular way.

SELECT tbTasks.projectId, 
       SUM(tbTasks.taskTime) AS totalTime, 
       tbProjects.projectName 
FROM tbTasks LEFT OUTER JOIN tbProjects
       ON tbTasks.projectId = tbProjects.projectId 
GROUP BY tbTasks.projectId 
UNION 
SELECT tbProjects.projectId, 
       SUM(tbTasks.taskTime) AS totalTime, 
       tbProjects.projectName   
FROM tbProjects LEFT OUTER JOIN tbTasks 
      ON tbProjects.projectId = tbTasks.projectId 
GROUP BY tbTasks.projectId 
ORDER BY tbProjects.created DESC

Only this does not work, I get a SQL syntax error. What am I doing wrong? Is there a better way to achieve my goal?

Answer 1

, SQLite RIGHT OUTER FULL OUTER, LEFT OUTER JOIN, , . tbProjects .

SELECT tbProjects.projectId, 
       COALESCE(SUM(tbTasks.taskTime), 0) AS totalTime, 
       tbProjects.projectName 
FROM tbProjects
    LEFT OUTER JOIN tbTasks ON tbProjects.projectId = tbTasks.projectId
GROUP BY tbProjects.projectId 
ORDER BY tbProjects.created DESC

NULLS totalTime , , COALESCE() 0.