Using Inner Join correctly and aggregating results

Question

In my comic book, I have 4 tables: Aluno, Ano, Matérias and Nota. They are mounted as follows:

Tabela    :: Colunas
----------------------------------------------------------
Aluno     :: AlunoID     | AlunoNome    | AnoID
Ano       :: AnoID       | AnoNome
Materia   :: MateriaID   | MateriaNome  | AnoID
Nota      :: NotaID      | AlunoID      | MateriaID | Nota

I’m trying to use a inner join to unite all data:

Aluno a INNER JOIN      Ano b on ( a.AnoID   = b.AnoID   )
        INNER JOIN  Materia c on ( b.AnoID   = c.AnoID   )
        INNER JOIN     Nota d on ( a.AlunoID = d.AlunoID ) 

In fact, I’m able to put the data together! Only I’m having a problem - on the table Aluno, I have two records. When I use the echo to display the students, the names repeat to each subject and each note.

It looks something like this:

Aluno1 Ano1 Materia1 Nota1
Aluno1 Ano1 Materia2 Nota1
Aluno1 Ano1 Materia1 Nota2
Aluno1 Ano1 Materia2 Nota2

Aluno2 Ano2 Materia3 Nota3
Aluno2 Ano2 Materia4 Nota4
Aluno2 Ano2 Materia3 Nota3
Aluno2 Ano2 Materia4 Nota4

As can be seen in the example above, the query result repeats the name and matter, and toggles the display of the rest of the results!

I would like the result to be something like this:

Aluno1 Ano1 Materia1 => Nota1 / Materia2 => Nota2 
Aluno2 Ano2 Materia3 => Nota3 / Materia4 => Nota4

As you can see, the name now repeats only once and the other information relating to that name is placed beside it!

I know I can solve this "problem" by increasing the number of columns in the table aluno, placing multiple columns calls notai, guy nota1, nota2, nota3, but that way I’ll be very limited.

My doubt then is:

How to join the tables in such a way that the names do not keep repeating and the information related to the student appear beside?

I would like to establish the real relationship, since a student belongs to a specific year, that specific year has X amount of subjects and each student of each year earned Y grade in X subject.

I’m not able to do it, it’s getting all dolled up like in the above examples.

Answer 1

The first problem I see is that you forgot a detail in your INNER JOIN:

INNER JOIN     Nota d on ( a.AlunoID = d.AlunoID )

That is, the notes pulled on each tuple are those that relate to the student, and only with the student, being free with respect to the subjects. You forget to relate the notes to the subjects, so the result brings notes along with matters that have no relation to each other. That is, he ends up making a Cartesian product between the disciplines and grades of a student.

That would be right:

INNER JOIN     Nota d on ( a.AlunoID = d.AlunoID AND c.MateriaID = d.MateriaID)

As for the merging of the various materials and notes, it is something that I do not recommend doing, because I believe that only makes things more difficult and I think that this idea starts from a flaw in the interpretation of the meaning of the results. However, in case you want to insist on that, I think the solution to your problem is to use the GROUP_CONCAT:

SELECT
    a.AlunoNome,
    b.AnoNome,
    GROUP_CONCAT(
        CONCAT(c.MateriaNome, ' => ', d.Nota)
        ORDER BY c.MateriaNome
        SEPARATOR ' / '
    ) AS Boletim
FROM Aluno a
INNER JOIN Ano b ON a.AnoID = b.AnoID
INNER JOIN Materia c ON b.AnoID = c.AnoID
INNER JOIN Nota d ON a.AlunoID = d.AlunoID AND c.MateriaID = d.MateriaID
GROUP BY a.AnoID, a.AlunoID;