relate two tables listing only last php record

Question

I have a jig table as follows:

|  materia  | provaID | matricula | questao | resposta |
 matematica    303      211834      quest1      A    
 matematica    303      211834      quest2      C    
 matematica    303      211834      quest3      B    
 matematica    303      211834      quest4      D   
 matematica    304      211834      quest1      C   
 matematica    304      211834      quest2      A    
 matematica    303      741431      quest1      B   
 matematica    303      741431      quest2      D   
 matematica    303      741431      quest3      C   
 matematica    303      741431      quest4      E   

The "proof" table looks like this:

|  id  |  materia  |
  303    matematica
  304    matematica

What I need is to relate the two tables, but list only the "answers" of the last "proof" but of each matricule thus staying:

                    |  materia  | provaID | matricula | questao | resposta |

**Ultimo desse aluno** matematica    304      211834      quest1      C   
**Ultimo desse aluno** matematica    304      211834      quest2      A

**Ultimo desse aluno** matematica    303      741431      quest1      B   
**Ultimo desse aluno** matematica    303      741431      quest2      D   
**Ultimo desse aluno** matematica    303      741431      quest3      C   
**Ultimo desse aluno** matematica    303      741431      quest4      E 

That post /questions/175280/como-montar-um-select-para-retornar-a-ultima-altera%C3%A7%C3%a3o-de-status-de-every-id did not help me because it returns ONE row of each ID, I need it to return all the LAST lines of each matricule, getting as above.

See, this code returns to me part of what I need:

    $gabaritoByAluno = $db->prepare("SELECT gabarito.*, prova.* FROM gabarito INNER JOIN prova ON (gabarito.provaID != prova.id) WHERE gabarito.materia = ? AND gabarito.matricula = ?");
$gabaritoByAluno->execute(array("AUTORIDADE ESPIRITUAL", "218113"));

 while ($dadosByAlunos = $gabaritoByAluno->fetch()) {
     echo $dadosByAlunos['questao'] . '<br>';
 }

it returns me all questions, I want to return only the last questions that were quest1 and quest2 of provaID 304

Answer 1

Dividing the problem into parts

With that query we get the biggest provaID of each matricula:

SELECT matricula, MAX(provaID) AS prova
FROM gabarito
GROUP BY matricula

The GROUP BY causes only one line to be returned for each registration, and the MAX get the biggest provaID of each registration individually.

Getting the questions of each test

To get the questions of the proofs returned with the query above, just one LEFT JOIN of the results obtained, with the table templates:

SELECT   gabarito.materia,
         gabarito.provaID,
         gabarito.matricula,
         gabarito.questao,
         gabarito.resposta

FROM  ( SELECT   matricula, MAX(provaID) AS prova
        FROM     gabarito
        GROUP BY matricula
      ) AS ultima
      LEFT JOIN gabarito
        ON  ultima.prova     = gabarito.provaID
        AND ultima.matricula = gabarito.matricula

ORDER BY matricula, questao

See working on SQL Fiddle.

For those who do not know, the query used in parentheses instead of the name of another table is known as subquery. When the subquery behaves as if it were a real table, is more specifically known as "derived table".

In other words, this...:

FROM  ( SELECT   matricula, MAX(provaID) AS prova
        FROM     gabarito
        GROUP BY matricula
      ) AS ultima
      LEFT JOIN gabarito

...behaves as if there really was a table called ultima with each other’s latest evidence, and we had just done:

FROM  ultima
      LEFT JOIN gabarito

Recommended readings:

What is the difference between INNER JOIN and OUTER JOIN?

How to mount a SELECT to return the last status change of each id?

GROUP BY last entry