Romain Romain - 1 month ago 6
SQL Question

COUNT() combined with INNER/LEFT JOIN not working

I'm stuck with a mysql query which appears to be too complex for my poor level.
Any help to solve this issue would be highly appreciated.

I have 3 tables:

_ members: list of all members

_ friends: list of all friends each members has, using 2 fields: friends.id_member contains the member id and friends.id_friend contains the id of the corresponding friend.

_ comments: list of all comments posted by members (using field comments.id_member to join this table to the table members)

I would like:

_ (1): to display all friends a member might have (in this example the member number 35; it works well)

SELECT friends.*, members.*
FROM friends
INNER JOIN members ON friends.id_friend=members.id
WHERE friends.id_member='35'


_ (2): using the same mysql query, get the number of comments each of its friends MIGHT have posted (not all friends have posted comments; and here is my issue).
I have tried this:

SELECT friends.*, members.*,
COUNT(comments.id_member) AS nb_comments
FROM friends
INNER JOIN members ON friends.id_friend=members.id
INNER JOIN comments ON comments.id_member=members.id
WHERE friends.id_member='35'


-> I only get friends who had posted comments (friends with no comment posted, which should be here, are ignored by this query), and the number of comments (nb_comments) I get is wrong.
Another bug; the query return 1 row in cases the member has no friend at all.

nb: a 'LEFT JOIN comments ON comments.id_member=members.id' does not solve anything.

Answer

One method to do what I think you want is to use a subquery:

SELECT f.*, m.*,
       (SELECT COUNT(*)
        FROM comments c
        WHERE c.id_member = m.id
       ) as nb_comments
FROM friends f INNER JOIN
     members m
     ON f.id_friend = m.id 
WHERE f.id_member = 35;

When you add COUNT() to the SELECT, you turn the query into an aggregation query. Without a GROUP BY exactly one row is returned.

Notes:

  • The use of table aliases simplifies the query, making it easier to write and to read.
  • Single quotes should not be used around a numeric constants. I assume id_member is declared as a number.
  • The correlated subquery will add the count onto each row.
  • You should get in the habit of explicitly listing the columns you want. You may get unexpected results if the two tables have columns with the same name.
Comments