MySQL how to count in a left join with where clause only for count?

Question

I have this two tables: ritter(ritterId, ritterName) and quest(questID, ritterID, datum).

Now my question is, how can i show all from ritter with an extra column which shows the count of quests for the ritter in the current week?

I tried this:

select r.*, count(q.ritterID) from ritter as r left join quest as q on r.ritterID = q.ritterID group by r.ritterID;

This works but without the where cause for the current week.

If i do: select r.*, count(q.ritterID) from ritter as r left join quest as q on r.ritterID = q.ritterID where weekofyear(datum) = weekofyear(now()) group by r.ritterID;

then it shows only the ritter which did a quest in the current week but i want to that it shows all the ritter.

Output:

How it is:

How it should be:

Table ritter:

and the table quest:

How can i solve this?

I hope you can understand what i mean and what i want. Sorry for my english.

Answer 1

You are not far from the solution, just move the date filter from the where criteria into the join condition. This way it only applies to the table on the right hand side of the join, not the entire resultset:

select r.*, count(q.ritterID)
from ritter as r left join quest as q
   on r.ritterID = q.ritterID and weekofyear(datum) = weekofyear(now())
group by r.ritterID

Since ritter name is functionally dependent on ritter id, your query will be fine on the recent versions of MySQL. In older versions, however, you may get into trouble if only_full_group_by sql mode is set. So, it may be better to add r.ritterName to the group by clause.