I have a pivot table that holds 3 fk, user_id, subject_id and level_id ..
What I want is to group the user to a subject and all levels .. in the pivot table is stored as follows:
|usuario_id|materia_id|nive_id|
| 1 | 1 | 1 |
| 1 | 1 | 2 |
| 1 | 1 | 3 |
| 1 | 1 | 4 |
and what I want to show is this, example:
Name : User = > Meteria : Biology => Levels : 7-A, 7-B, 7-C, 7-D
Thanks in advance.
Here is the script. I already tried it and it works, but anyway, you let me know anything. Greetings!
Edit: I'll explain the steps or what I did to make it clearer.
First of all you should make your query with SQL INNER JOIN . With this you will be able to obneter the fields "name" instead of the ID. Up to here we do not group only we show the data of repetitive form, as for the column "levels". Now the important thing is to use MySQL GROUP_CONCAT () . This function is what allows us to group and concatenate the levels. Finally, use SQL GROUP BY Statement . The latter is very important if it is not going to show all the users. I hope it has been clear, but you can do your queries or read the links that you leave. Greetings!
select u.nombre as Nombre, m.nombre as Materia, group_concat(distinct n.nombre) as Niveles from pivote p
inner join usuarios u on p.usuario_id = u.id
inner join materias m on p.materia_id = m.id
inner join niveles n on p.nivel_id = n.id
group by u.nombre, m.nombre;
you will have to use INNER JOIN , to join the tables; and DISTINCT for repeated data.