GROUP BY and get the columns values into single row column

Question

i was thinking if possible to get all the values of using GROUP BY room. example i have a tables named subject , room and classroom

table subject

   subject_id | subject_name
    -------------------
        1     | math
        2     | science
        3     | english

table room

    room_id | room_name
   -------------------
       1    | sunflower
       2    | cornflower
       3    | redflower

table classroom

    id | room_id | subject_id 
   -------------------
    1  | 1       | 1          
    2  | 1       | 2
    3  | 1       | 3

now the query. not sure if the query im used was correct or enough querying.. but the outputed query show expecting not that

SELECT * FROM classroom 
LEFT JOIN subject ON classroom.subject_id = subject.subject_id
LEFT JOIN room ON classroom.room_id = room.room_id 
GROUP BY room_name

now the output i want to expecting like this

classroom

room_name | subject_name 
------------------------
sunflower | math
          | science
          | english

it should grouping by sunflower into one row column

Answer 1

GROUP BY is designed to reduce the number of rows and to enable aggregations for those rows. In your case the following query, without group by produces:

SELECT room_name, subject_name
FROM classroom 
LEFT JOIN subject ON classroom.subject_id = subject.subject_id
LEFT JOIN room ON classroom.room_id = room.room_id 

+-----------+--------------+
| room_name | subject_name |
+-----------+--------------+
| sunflower | english      |
| sunflower | math         |
| sunflower | science      |
+-----------+--------------+

So there isn't much point in using GROUP BY, but if we do, this is the result:

SELECT room_name, subject_name
FROM classroom 
LEFT JOIN subject ON classroom.subject_id = subject.subject_id
LEFT JOIN room ON classroom.room_id = room.room_id 
GROUP BY room_name, subject_name

+-----------+--------------+
| room_name | subject_name |
+-----------+--------------+
| sunflower | english      |
| sunflower | math         |
| sunflower | science      |
+-----------+--------------+

Note that the values are presented in both columns for every row. This IS both expected AND DESIRED.

GROUP BY is not a "presentation" tool that will suppress output of values in the first column, that is something that a "report tool" or "presentation layer" would be expected to handle.

demo