syntax for 2 left joins in sqlite-CodePudding

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.

CodePudding user response：

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.