SQLite3 Simulate RIGHT OUTER JOIN with LEFT JOINs and UNION

Question

I have the following select statement where I need to sum each task from table tbTasks and group them by projectId from table tbProjects in order to get a record like this:

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

The query looks like this:

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 executes fine but with one problem, if a project has no task associated with it, then I get no record back at all (where I want to get projectId, projectName, and 0 or NULL for totalTime). So, in order to right join on the table tbProjects SQLite3 forces me to do it in a round-about 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 an SQL syntax error. What am I doing wrong? Is there a better way to achieve my goal?

Bramwell

Related: FULL OUTER JOIN with SQLite – Merlon

Answer 1

Even though SQLite hasn't implemented RIGHT OUTER or FULL OUTER, it does have LEFT OUTER JOIN, which should do what you'd like. Just have tbProjects be on the left.

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

You get NULLS in totalTime for projects that don't have any tasks, and the call to COALESCE() replaces the null with a 0.

Bergeman

is there an eval function to return 0 instead of NULL? – Bramwell

FYI, that fixed the issue. I just did a variable check while I'm iterating through the query results and if null then the variable is set to 0. Thanks for the help, I was clearly over thinking it. – Bramwell

@sadmicrowave: Sorry, I didn't see your second question. Yes, there is a very easy way to return 0 instead of NULL. See my edited answer. – Bergeman

Answer 2

From version 3.39.0, SQLite supports right and full outer join:

SELECT *
FROM t1 RIGHT JOIN t2
  ON t1.id = t2.id;

Thanks to Lukasz Szozda for their answer.

Merlon