syntax for 2 left joins in sqlite

Question

This query runs fine and gives me an output like this:

[(1, 9), (2, 12), (4, 14), (6, 14)]

    query = """
    SELECT users.id,
       count(tasks.userId)
    FROM users
    LEFT JOIN tasks ON users.id = tasks.userId
    WHERE tasks.completed = FALSE
    GROUP BY users.id
    """

However, when I add another left join, it does not give me accurate results:

    query = """
    SELECT users.id,
       count(tasks.userId), count(songs.userId)
    FROM users
    LEFT JOIN tasks ON users.id = tasks.userId
    LEFT JOIN songs ON users.id = songs.userId
    WHERE tasks.completed = FALSE
    GROUP BY users.id
    """

The result should look like this:

[(1, 9, 10), (2, 12, 10), (4, 14, 10), (6, 14, 10)]

but instead, my result looks like this:

[(1, 90, 90), (2, 120, 120), (4, 140, 140), (6, 140, 140)]

which looks like the value of tasks x 10as both, count(tasks) and count(song) what am I missing out on?

The idea of count(tasks.userId)was to find the number of tasks where the userId matched.

Does this answer your question? [Two SQL LEFT JOINS produce incorrect result](https://stackoverflow.com/questions/12464037/two-sql-left-joins-produce-incorrect-result) — philipxy, Apr 18 '22 at 09:13

score 1 · Accepted Answer · answered Apr 16 '22 at 19:47

1

Aggregate separately in tasks and songs and then join users to the results of the aggregations:

SELECT u.id, 
       COALESCE(t.count_tasks, 0) count_tasks,
       COALESCE(s.count_songs, 0) count_songs
FROM users u
LEFT JOIN (
  SELECT userId, COUNT(*) count_tasks
  FROM tasks 
  WHERE completed = FALSE
  GROUP BY userId
) t ON u.id = t.userId
LEFT JOIN (
  SELECT userId, COUNT(*) count_songs
  FROM songs  
  GROUP BY userId
) s ON u.id = s.userId;

I'm not sure if you actually want LEFT joins (at least for the table tasks), because in your code, the 1st query that you say returns what you expect, although it contains a LEFT join, the join is actually an INNER join because the condition WHERE tasks.completed = FALSE returns only the matching rows.

answered Apr 16 '22 at 19:47

forpas

160,666
10
38
76

why do we use coalesce here? any alternatives? – x89 Apr 16 '22 at 21:00
@x89 you are using LEFT joins. In the results there may exist users with no tasks or no songs related to them. In this case the result for count_tasks or count_songs would be null. COALESCE() is used to return 0 instead of null. – forpas Apr 16 '22 at 21:08
ah yes. how could we modify this in case of inner joins then – x89 Apr 16 '22 at 23:31
@x89 use INNER JOIN instead of LEFT JOIN and `SELECT u.id, t.count_tasks, s.count_songs ....` – forpas Apr 17 '22 at 06:17

syntax for 2 left joins in sqlite

1 Answers1