Jan - 1 year ago 41

MySQL Question

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 Source

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
```