MYSQL select recursive between 2 tables?

Question

MYSQL select recursive between 2 tables?

Navigation

#1 by (2 votes)

2

I have two tables (exam) and (note) that are related to each other by the column "id" of the table (exam) and the column "id_examen" of the table (note).

I want to get the average of the grades for each exam.

For example, the exam with id = 1 - > "Surprise test" in the table (exam), I want to obtain the average of all the records in the table (note) that have id_examen = 1.

TABLA EXAMEN

    id  |  nombre                      |   fecha          |    id_perfil
--------------------------------------------------------------------------
    1   | 'Test sorpresa'              | '2018-04-21'     |       '1'
    2   | 'Evaluación 1º trimestre'    | '2018-11-25'     |       '1'
    3   | 'Fichas de grafomotricidad'  | '2018-10-15'     |       '1'

TABLA NOTA

id   | valor |  id_examen
------------------------
1    | 5     |  1
1    | 6     |  1
1    | 8     |  1
1    | 4     |  2
1    | 8     |  2

This is the query I'm trying:

SELECT t5.nombre, AVG(t3.valor) 
FROM examen as t5, (  SELECT t1.valor
                      FROM nota as t1 
                      WHERE t1.id_examen = t5.id
                   ) as t3
WHERE t5.id_perfil=1 ORDER BY t5.fecha DESC LIMIT

But I get this error: # 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 6

I want to get:

Test sorpresa 6,7

Evaluación 1º trimestre 6

mysql

asked by Antonio Ruiz 03.06.2018 в 09:49

source

1 answer

I do not know how I can get out of the if after the continue, since an infinite cycle is done Icon associated with a file type in Linux

score 2 · Accepted Answer

Now it is understood.

You can achieve it simply with this query, which does not do more than use the related columns in both tables to make a JOIN .

If you want the average of each exam id

Combine AVG with GROUP BY in this way:

SELECT 
    e.nombre, 
    AVG(n.valor) average
FROM examen e
INNER JOIN nota n ON e.id=n.id_examen 
GROUP BY e.id
ORDER BY e.fecha DESC;

Result:

nombre                         average
-----------------------------------------------
Evaluación 1º trimestre        6,0000
Test sorpresa                  6,3333

Filtering for a specific test

Almost the same query, but changing the GROUP BY to WHERE .

SELECT 
    e.nombre, 
    AVG(n.valor) average
FROM examen e
INNER JOIN nota n ON e.id=n.id_examen
WHERE e.id=1;

The result would be:

nombre              average
-------------------------------
Test sorpresa       6,3333

Proof of concept

Here you can see a DEMO and test with real data.