Problem in MySQL query

Question

Problem in MySQL query

Navigation

#1 by (3 votes)

4

I have a problem with a MySQL query. In it I have 3 tables that I need to cross:

Drawing table:

id
concurso_id
customer_id

Contest table:

id 
titulo   
criterioDiasCorridos
criterioNotaMedia
criterioMinimoVotos

"notadesenho" table:

customer_id
desenho_id
concurso_id
nota

This is a voting system where I need to list all the DRAWINGS of the CONTEST X (concurso.id = X) and SOMAR amount of votes that the drawing had (notadesenho.desenho_id) and display the AVERAGE of votes as well (nottasenho .nota).

I made the following query:

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's only returning 1 drawing when I ask to list all of them from contest X.

I suspect the problem is in JOIN connections. But I can not see the solution.

mysql sql

asked by anonymous 21.08.2014 / 17:43

1 answer

Organization and maintenance of Bootstrap Difference between table, entity and behavior

score 3 · Accepted Answer

The problem with your query is COUNT() and AVG() .

They are aggregate 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