join from different tables gives same result, but it shouldn't

Question

This is the code I'm using and I'm having a problem with it.

SELECT `disenos`.*, `votos`.`valoracion`, `usuario_contacto`.`nombres`, 
`usuario_contacto`.`apellidos`, `users`.`username`, `users`.`avatar` as usuario_avatar, 
COUNT(comentarios.id_comentario) as comentarios, 
COUNT(votos.id_usuario) as votos 
FROM (`disenos`) 
LEFT JOIN `votos` ON `votos`.`id_diseno` = `disenos`.`id_diseno` AND votos.id_usuario = 1 
LEFT JOIN `users` ON `users`.`id_usuario` = `disenos`.`id_usuario` 
LEFT JOIN `usuario_contacto` ON `usuario_contacto`.`id_usuario` = `disenos`.`id_usuario` 
LEFT JOIN `comentarios` ON `comentarios`.`id_diseno` = `disenos`.`id_diseno` 
WHERE `disenos`.`estado` = 1 AND `disenos`.`votacion` = 1 
GROUP BY `disenos`.`id_diseno` LIMIT 5

both COUNTs are giving me the same result although they shouldn't. If I get rid of the

LEFT JOIN `comentarios` ON `comentarios`.`id_diseno` = `disenos`.`id_diseno`

the COUNT(votos.id_usuario) as votos gives the correct result.

I've been dealing with this for the last 4 hours and can't seem to get it solved so I appeal to your wisdom :D

Thanks in advance!

Answer 1

the solution was to add a subquery :D thanks!!

SELECT 
    `disenos`.*, 
    `usuario_contacto`.
    `nombres`, 
    `usuario_contacto`.
    `apellidos`, 
    `users`.`username`, 
    `users`.`avatar` as usuario_avatar, 
    `total_coment`, 
    COUNT(votos.id_usuario) as votos 
FROM (`disenos`) 
LEFT JOIN `votos` ON `votos`.`id_diseno` = `disenos`.`id_diseno` 
LEFT JOIN `users` ON `users`.`id_usuario` = `disenos`.`id_usuario` 

LEFT JOIN 
    ( 
        SELECT id_diseno, COUNT(id_comentario) as total_coment 
        FROM `comentarios` 
        GROUP BY `id_diseno` 
    ) `comments`

ON `comments`.`id_diseno` = `disenos`.`id_diseno`
LEFT JOIN `usuario_contacto` ON `usuario_contacto`.`id_usuario` = `disenos`.`id_usuario` 
WHERE `disenos`.`estado` = 1 
AND `disenos`.`votacion` = 1 
GROUP BY `disenos`.`id_diseno` 
LIMIT 5

EDIT: correcting a typo, sorry