Jan Jan - 3 months ago 6
MySQL Question

SQL query to mark tasks done from a specific user

These are my tables:

Table tasks

id label
----------
1 Task 1
2 Task 2
3 Task 3
4 Task 4
5 Task 5
6 Task 6
7 Task 7
7 Task 8


Table user_tasks

id id_user id_task
------------------
1 1 1
2 1 2
3 1 4
4 2 1
5 3 1


I have this query which I thought was working:

SELECT t.id, t.label as text, IF(u.id_user IS NULL, 0, 1) as done
FROM tasks AS t
LEFT JOIN user_tasks AS u ON t.id = u.id_task
WHERE u.id_user = 1
OR u.id_user IS NULL
ORDER BY id DESC


So if I run that query, I get this successful response:

id text done
----------------
8 Task 8 0
7 Task 7 0
6 Task 6 0
5 Task 5 0
4 Task 4 1
3 Task 3 0
2 Task 2 1
1 Task 1 1


But if I put another user ID that doesn't have any task yet completed, like for example 5 I get this result:

id text done
----------------
8 Task 8 0
7 Task 7 0
6 Task 6 0
5 Task 5 0
3 Task 3 0


So all the tasks, minus the ones that are already set for a different user on the user_tasks table.

What am I doing wrong?
Thanks!

Answer

Move u.id_user = 1 to the ON clause, and remove the WHERE clause (including u.id_user IS NULL.)

SELECT t.id, t.label as text, IF(u.id_user IS NULL, 0, 1) as done
FROM tasks AS t
    LEFT JOIN user_tasks AS u ON t.id = u.id_task AND u.id_user = 1
ORDER BY id DESC