una - porque se dañan las tablas en mysql

COUNT() combinado con INNER/LEFT JOIN no funciona

Question

Estoy atascado con una consulta de MySQL que parece ser demasiado compleja para mi nivel pobre. Cualquier ayuda para resolver este problema sería muy apreciada.

Tengo 3 tablas:
_ miembros: lista de todos los miembros
_ amigos: lista de todos los amigos que tiene cada miembro, usando 2 campos: friends.id_member contiene el ID de miembro y friends.id_friend contiene el id del amigo correspondiente.
_ comentarios: lista de todos los comentarios publicados por los miembros (utilizando field comments.id_member para unir esta tabla a los miembros de la tabla)

Me gustaría:
_ (1): para mostrar todos los amigos que un miembro pueda tener (en este ejemplo, el miembro número 35; funciona bien)

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

_ (2): usando la misma consulta mysql, obtenga la cantidad de comentarios que cada uno de sus amigos PODRÍA haber publicado (no todos los amigos han publicado comentarios, y aquí está mi problema). He intentado esto:

    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''

-> Solo tengo amigos que publicaron comentarios (los amigos que no publicaron ningún comentario, que deberían estar aquí, son ignorados por esta consulta), y la cantidad de comentarios (nb_comments) que obtengo es incorrecta. Otro error; la consulta devuelve 1 fila en caso de que el miembro no tenga ningún amigo.

nb: a ''IZQUIERDA UNIR comentarios ON comments.id_member = members.id'' no resuelve nada.

Answer 1

Un método para hacer lo que creo que desea es usar una subconsulta:

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;

Cuando agrega COUNT() al SELECT , convierte la consulta en una consulta de agregación. Sin un GROUP BY se devuelve exactamente una fila.

Notas:

• El uso de alias de tabla simplifica la consulta, facilitando la escritura y la lectura.
• Las comillas simples no se deben usar alrededor de las constantes numéricas. Supongo que id_member está declarado como un número.
• La subconsulta correlacionada agregará el recuento a cada fila.
• Debería tener el hábito de listar explícitamente las columnas que desea. Puede obtener resultados inesperados si las dos tablas tienen columnas con el mismo nombre.