relate two tables listing only last php record

Question

relate two tables listing only last php record

Navigation

#1 by (3 votes)

2

I have a template 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" of each enrollment:

                    |  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

This post #

Look, this code returns 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 the questions, I want to return only the last questions that were quest1 and quest2 of the testID 304

php mysql

asked by anonymous 08.06.2018 / 02:32

1 answer

Querry Lazy Loading on EF Code First About rowspan

score 3 · Accepted Answer

Dividing the problem into parts

With this query we get the highest provaID of each matricula :

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

The GROUP BY returns only one row for each enrollment, and MAX gets the highest provaID of each enrollment individually.

Getting the questions of each test

To get the test questions returned with query above, just a LEFT JOIN of the results obtained, with the templates template itself:

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 in place of the name of another table is known as subquery . When subquery behaves as if it were an actual table, it is more specifically known as a "Derived table".

In other words, this ...:

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

... It behaves as if there were actually a table called ultima with the last evidence of each, and we had simply done:

FROM  ultima
      LEFT JOIN gabarito