Romain Romain - 1 year ago 53
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 Source

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.
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download