Problem in Mysql query

Question

I’m having a problem with a Mysql query. In it I have 3 tables I need to cross:

Table "drawing":

id
concurso_id
customer_id

Table "competition":

id 
titulo   
criterioDiasCorridos
criterioNotaMedia
criterioMinimoVotos

Table "noteworthy":

customer_id
desenho_id
concurso_id
nota

It is a voting system where I need to list all the DRAWINGS of THE CONTEST X (contest.id = X) and ADD amount of votes that the drawing had (noteworthy.drawing.drawing.id) and display the AVERAGE of votes also (noteworthy.note).

I made the following consultation:

SELECT
    d.id,
    d.nome,
    d.descricao,
    d.arquivo,
    d.tags,
    d.concurso_id,
    c.criterioNotaMedia,
    c.criterioMinimoVotos,
    c.criterioDiasCorridos,
    COUNT(n.desenho_id) as quantidadeVotos,
    IFNULL(AVG(n.nota), 0) as notaDesenho
FROM desenho d
LEFT JOIN concurso c ON d.concurso_id = c.id 
LEFT JOIN notadesenho n ON d.id = n.desenho_id
WHERE c.id = $idConcurso

But it is returning only 1 drawing when I ask to list all from the X contest.

I suspect the problem is in the JOIN links. But I’m not being able to see the solution.

Answer 1

The problem in your query is in COUNT() and in the AVG().

They are aggregated functions and should be used together with GROUP BY to produce the expected result.

Your query should look something like this (Grouping the drawings by id):

SELECT
    d.id,
    d.nome,
    d.descricao,
    d.arquivo,
    d.tags,
    d.concurso_id,
    c.criterioNotaMedia,
    c.criterioMinimoVotos,
    c.criterioDiasCorridos,
    COUNT(n.desenho_id) as quantidadeVotos,
    IFNULL(AVG(n.nota), 0) as notaDesenho
FROM desenho d
LEFT JOIN concurso c ON d.concurso_id = c.id 
LEFT JOIN notadesenho n ON d.id = n.desenho_id
WHERE c.id = $idConcurso
GROUP BY d.id