Mysql subquery in from clause give syntax errors

Question

I've a db where I store the answers given by users (table 'risposta_utente') to questions belonging to different lessons (table 'lezioni'). I want to get the lesson with the maximum average of the votes obtained by an user answering its questions.

ID_corso_sede_utente uniquely identify an user following the lessons of one course (because there are more then one course).

SELECT titolo, MAX(voto) as voto_max
FROM (
    SELECT AVG(voto_grezzo) as voto, ID_lezione
    FROM risposta_utente
    WHERE ID_corso_sede_utente = 260, risposta_utente.attivo = 1
    GROUP BY ID_lezione
) AS voti 
JOIN lezioni
ON lezioni.ID_lezione=voti.ID_lezione

Mysql signal errors on lines 2, 3 and 7 (around the brackets enclosing the subquery). In particular it say:

expecting expression, "(" found (line 2);
unexpected token near "(" (line 3);
unexpected token near ")" (line 7);
unknown keyword near "AS" (line 7);
unexpected token near "voti" (line 7);

WHERE ID_corso_sede_utente = 260, risposta_utente.attivo = 1 should probably be WHERE ID_corso_sede_utente = 260 AND risposta_utente.attivo = 1 — Joachim Isaksson
– Joachim Isaksson, Commented Oct 29, 2016 at 8:29

Srihari Karanth · Accepted Answer · 2016-10-29 08:31:30Z

1

'Where' clause should be separated by "and" and not comma.

SELECT titolo, MAX(voto) as voto_max
FROM (
    SELECT AVG(voto_grezzo) as voto, ID_lezione
    FROM risposta_utente
    WHERE ID_corso_sede_utente = 260 **and** risposta_utente.attivo = 1
    GROUP BY ID_lezione
) AS voti 
JOIN lezioni
ON lezioni.ID_lezione=voti.ID_lezione

And one more thing, if you are running this is unix mysql console then adding tab will throw an error. Remove the blank spaces before each line and try

SELECT titolo, MAX(voto) as voto_max
FROM (
SELECT AVG(voto_grezzo) as voto, ID_lezione
FROM risposta_utente
WHERE ID_corso_sede_utente = 260 **and** risposta_utente.attivo = 1
GROUP BY ID_lezione
) AS voti 
JOIN lezioni
ON lezioni.ID_lezione=voti.ID_lezione

Or try executing it as a single line.

answered Oct 29, 2016 at 8:31

Srihari Karanth

2,1772 gold badges26 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Davide Visentin Over a year ago

The problem was the AND. Anyhow thanks also for the second hint; I've never launched query in unix mysql console, but that's an useful info.

sriramkumar · Accepted Answer · 2016-10-29 08:34:27Z

0

where command should be as expression.

either

WHERE ID_corso_sede_utente = 260 AND risposta_utente.attivo = 1

or

WHERE ID_corso_sede_utente = 260 OR risposta_utente.attivo = 1

based on your business logic

answered Oct 29, 2016 at 8:34

sriramkumar

1642 silver badges16 bronze badges

Collectives™ on Stack Overflow

Mysql subquery in from clause give syntax errors

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related