MySQL COUNT query in join

Question

I have two tables.

The first table is the member table

MEMBER TABLE
ID    |  NAME
1     |  User
2     |  Another User
3     |  Some other User

and the second table is friends

FRIENDS TABLE
ID    |  member_id    |  Friend Name    |  Notified
1     |  1            |  Friend #1      |  0
2     |  1            |  Friend #1      |  1
3     |  2            |  Friend #1      |  0
4     |  1            |  Friend #1      |  1
5     |  2            |  Friend #1      |  1

What I like to do is to get the member table information but also to get the total notified friends for each member.

What I have done until now is that

SELECT
    M.ID
    M.NAME
    COUNT(F.notified)
FROM
    MEMBER AS M
LEFT JOIN
    FRIENDS AS F
ON
    F.member_id = M.id
WHERE
    F.notified = 1
GROUP BY
    M.id

But this, not working for me, because if I have a member with not notified friends, the query does not included in the results.

In the above code for example the member with ID 3 will not included in my results.

Any idea on how to modify that query in order to return even members with no notified friends ?

Kind regards Merianos Nikos

Answer 1

You can do it with a subquery in this way :

SELECT
    M.ID,
    M.NAME,
    (SELECT  COUNT(F.notified) FROM FRIENDS AS F WHERE F.member_id = M.id AND F.notified = 1) AS NUMFRIENDS
FROM
    MEMBER AS M 
GROUP BY
    M.id

Answer 2

The WHERE F.notified = 1 condition cancels the LEFT join, making it work as an INNER join. Move the condition to the joining ON clause:

SELECT
    M.ID
    M.NAME
    COUNT(F.member_id)
FROM
      MEMBER AS M
  LEFT JOIN
      FRIENDS AS F
    ON
      F.member_id = M.id
    AND
      F.notified = 1
GROUP BY
    M.id ;