join table with empty results

Question

i have two tables: users and tasks

table users:

+----------+---------------------+
|    id    |      usrname        |
+----------+---------------------+
|    1     | user1               |
+----------+---------------------+
|    2     | user2               |
+----------+---------------------+
|    3     | user3               |
+----------+---------------------+
|    4     | user4               |
+----------+---------------------+

table tasks

+----------+-------------------+-------------------+-------------------+
|    id    |      users        |      task         |   calenderweek    |
+----------+-------------------+-------------------+-------------------+
|    1     | user1             | task1             | 48                |
+----------+-------------------+-------------------+-------------------+
|    2     | user1             | task2             | 49                |
+----------+-------------------+-------------------+-------------------+
|    3     | user2             | task1             | 50                |
+----------+-------------------+-------------------+-------------------+
|    4     | user1             | task3             | 50                |
+----------+-------------------+-------------------+-------------------+

now i want to display all users with their tasks for given calenderweek even if there is no entry for given calenderweek.

outout should look like this:

output for calenderweek(50):

+----------+-------------------+-------------------+
|    id    |      users        |      task         |
+----------+-------------------+-------------------+
|    1     | user1             | task3             |
+----------+-------------------+-------------------+
|    2     | user2             | task1             |
+----------+-------------------+-------------------+
|    3     | user3             | no task           |
+----------+-------------------+-------------------+
|    4     | user4             | no task           |
+----------+-------------------+-------------------+

i guess i need a combination of Left join and COALESCE but i never worked with them and my results are empty or my syntax is wrong.

i tried so many things that I can not think clearly anymore haha my query looks like this at the moment:

SELECT  *, COALESCE(tasks.task, 'no tasks') FROM users LEFT JOIN tasks ON users.usrname = tasks.users WHERE tasks.calenderweek = '50'

So what is the best way to do this?

Answer 1

You're almost there. This should do the trick:

SELECT  *, COALESCE(tasks.task, 'no tasks') FROM users LEFT JOIN tasks ON users.usrname = tasks.users AND tasks.calenderweek = '50'

The problem is that you filter on calenderweek in the WHERE clause. Therefore any users without a task are left out of the result. If you place the condition in the JOIN, you do select all users and all users without a task get no tasks.