Using Inner Join Correctly and Adding Results

Question

Using Inner Join Correctly and Adding Results

Navigation

#1 by (1 votes)

1

In my DB, 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 merge 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 getting the data together! I'm just having a problem - in the Aluno table, I have two records. When I use echo to display students, names are repeated for each subject and each grade.

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 subject, and toggles the display of the rest of the results!

I would like the result to look something like this:

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

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

I know that I can solve this problem by increasing the number of columns directly in the aluno table, putting several columns called notai , type nota1 , nota2 , nota3 , but in this way I will stay very limited.

My question then is:

How to merge the tables so that names are not repeated and information about the student appears next?

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 has earned Y grade in X subject.

I can not do this, it's getting all jammed up like in the examples above.

php mysql sql join

asked by anonymous 04.09.2016 / 04:02

1 answer

Onclick point to function with $ _SERVER CSS attribute with online text condition

score 1 · Answer 1

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

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

That is, the notes drawn in each tuple are those that relate to the student, and only with the student, being free in relation to the subjects . You forget to relate the notes to the materials, so that the result brings notes along with materials that have no relation to each other. That is, it ends up making a Cartesian product between the disciplines and the notes of a student.

The correct would be this:

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

As for joining the various materials and grades, it's something I do not recommend doing because I believe it only makes things more difficult and I think this idea starts from a failure to interpret the meaning of the results. However, if you want to insist on this, I think the solution to your problem is to use 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;