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

ypercubeᵀᴹ · Accepted Answer · 2012-07-07 08:40:14Z

5

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 ;

answered Jul 7, 2012 at 8:40

ypercubeᵀᴹ

116k19 gold badges181 silver badges249 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

aleroot · Accepted Answer · 2012-07-07 08:41:02Z

3

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

answered Jul 7, 2012 at 8:41

aleroot

73k32 gold badges182 silver badges220 bronze badges

2 Comments

ypercubeᵀᴹ Over a year ago

Nice. But you don't even need the GROUP BY M.id with this approach.

Braiba Over a year ago

This will run a seperate subquery for every record in the members table, which will probably start to impact performance quite quickly as the members table grows. Ypercube's answer is the better way to do this, because it won't have those issues.

Collectives™ on Stack Overflow

MySQL COUNT query in join

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related