Use of JOINS and GROUP_CONCAT to show many related information into a cell

renatojosue

I have 3 tables: teachers, classrooms and teachers_classrooms where I associate teachers to their classrooms.

TEACHERS
========
teacher_id INT(4) PK
name VARCHAR(50)

Data examples:

(teacher_id; name)
1; Tony
2; Elliot


CLASSROOMS
==========
classroom_number INT(3) PK
floor INT(1)
color VARCHAR(15)

Data examples:
(classroom_number; floor; color)
10; 1; black
12; 1; yellow
34; 2; blue
37; 2; green
40; 2; orange
45; 1; red


TEACHERS_ROOMS
==============
teacher_id INT(4) PK
classroom_number INT(3) PK


Data examples:
(teacher_id; classroom_id)
1; 34
1; 45
2; 12
2; 34
2; 37


I’d like to have the following datatable output:

[code]


TEACHER | CLASSROOMS
- — - - - + - - - - - - - - - - - - - - - - - - - - - - -
Tony | 34, 2, blue ; 45, 1 red |
- — - - - + - - - - - - - - - - - - - — — - - - - - - - |
Elliot | 12, 1, yellow ; 34, 2, blue ; 37, 2, green |
- — - - - + - - - - - - - - - - - - - - - — - - - - - - -

[/code]

If I use the following SQL query I’am able to get this result, but I didn't find a way to use it on DataTables.

[code]
SELECT T.name, X.rooms FROM TEACHERS as T
LEFT JOIN (
SELECT TR.teacher_id, GROUP_CONCAT(C.classroom_number,',',C.floor,',',C.color SEPARATOR ' ; ' ) as rooms FROM TEACHERS_ROOMS TR
INNER JOIN CLASSROOMS as C on C.classroom_number = TR.classroom_number
GROUP BY TR.teacher_id
) as X ON X.teacher_id=T.teacher_id;
[/code]
Any help on this?!?

eduardorpm

Hi
I have the same problem o/

tks

allan

What exactly is it that you are looking to do? Is it simply display the information in the cell, or something more involved?

Thanks,
Allan

renatojosue

Hi Allan,
right now, I need only to display the info in the cell.
Actually, I'm using Editor to CRUD Teachers, Classrooms and Teacher_Rooms tables and the only missing part is to display the group_concat information into the Classroom cell.

Renato

allan

Hi Renato,

What I would suggest at the moment then is just directly running your query and dumping it into an array which you can then fire out at DataTables. Are you able to get the data front he query into a JSON format? If so, what does that format look like?

Allan