1

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!

1
2

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

1
  • See my answer in this similar question: Help with this query. There are several ways to solve this kind of problem and one of them is using subqueries (actually derived tables) as your solution. It's not very clean because you mixed methods, (subquery for one aggregation and group by again in the main query) but it is correct. Aug 31 '14 at 19:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.