Sub query between two mysql tables, for each row a set

Question

It is the following I have a table in mysql in which it keeps the name of the students and in this same table it has a column that keeps the code of the class in which the student is enrolled, I need a query that for each class returns me the set of students that belong to her, example

1st year: - Joao, maria, Ronaldo,

2nd year: - carlos, Fabio, Marcos,

the problem is that with Inner Join she returns me the class name repeatedly and for each position of the returned array it shows me only one student.

example:

1st year - July

1st year - Mary

1st year - Ronaldo

2nd year - carlos

2nd year - Fabio

2nd year - Ronaldo

how could I make a query that returns to me as the first example above?

as you can see in the images Robert Downey JR is in the same class that Carlos appeared but he appears in another part of the array I wanted him to appear in the same section as Carlos and so the 1st year primary class appeared only once

Answer 1

Separating via query

How it works

SELECT ano, GROUP_CONCAT(nome SEPARATOR ', ') FROM alunos GROUP BY ano;

Group by year using the GROUP BY and concatenate the result by separating by comma with the function GROUP_CONCAT

Using your query

SELECT T.turma, GROUP_CONCAT(A.nome SEPARATOR ', ') from turmas AS T INNER JOIN AS A on (T.idturma = A.idturma) GROUP BY T.turma;

Source

Separating with php

You can continue with your query, but separating in php as follows

//Array similar ao postado na pergunta, que vem atraves da consulta
$dados[0]['turma'] = '1º ano-primário';
$dados[0]['nome']  = 'Carlos Aparecido';
$dados[1]['turma'] = '1º ano-primário';
$dados[1]['nome']  = 'Robert Downy JR';
$dados[2]['turma'] = '2º ano-primário';
$dados[2]['nome']  = 'Stev Jobs';

$novosDados = [];
foreach($dados as $key => $data){

    //Utilizando o nome da turma como índice, conseguimos separar
    //O array de nomes por turma

    $novosDados[$data['turma']][]  = $data['nome'];
}

echo '<pre>';
var_export($novosDados);
echo '</pre>';

So each index is a class and within this index there is one array of names

Upshot

array (
  '1º ano-primário' => 
  array (
    0 => 'Carlos Aparecido',
    1 => 'Robert Downy JR',
  ),
  '2º ano-primário' => 
  array (
    0 => 'Stev Jobs',
  ),
)