Unir dos tablas (con una relación 1-M) donde la segunda tabla debe ser ‘aplanada’ en una fila

Dadas las siguientes tablas:

Estudiante

+----+-------+ | id | Name | +----+-------+ | 1 | Chris | | 2 | Joe | | 3 | Jack | +----+-------+ 

Inscripción

 +---------------+------------+-----------+----------+ | enrollment_id | student_id | course_id | complete | +---------------+------------+-----------+----------+ | 1 | 1 | 55 | true | | 2 | 1 | 66 | true | | 3 | 1 | 77 | true | | 4 | 2 | 55 | true | | 5 | 2 | 66 | false | | 6 | 3 | 55 | false | | 7 | 3 | 66 | true | +---------------+------------+-----------+----------+ 

Me gustaría lo siguiente

 +----+-------+-----------+-----------+-----------+ | id | Name | Course 55 | Course 66 | Course 77 | +----+-------+-----------+-----------+-----------+ | 1 | Chris | true | true | true | | 2 | Joe | true | false | NULL | | 3 | Jack | false | true | NULL | +----+-------+-----------+-----------+-----------+ 

Nota 1: Sé que mysql no puede tener columnas dinámicas (¡corrígeme si estoy equivocado!), Así que estoy contento con la consulta que comienza como:

 SELECT id, name, course_55, course_66, course_77 etc... 

Estoy contento con esto porque hay un número fijo de cursos (4 para ser exactos). Idealmente, me gustaría que sea dynamic; es decir, no tener que escribir manualmente cada curso en la cláusula SELECT.

Nota 2: Esto necesita mysql puro – No quiero recurrir a PHP.

La base de datos actualmente se encuentra en más de 10000 estudiantes con 10000+ * 4 inscripciones (ya que hay exactamente 4 cursos, y cada estudiante está en los 4 módulos).

Nota 3: Student.user_id está indexado y lo mismo ocurre con enrollment.enrollment_id, enrollment.student_id y enrollment.course_id.

 select s.id,s.name, max(case when e.course_id = 55 then complete else null end) as c55, max(case when e.course_id = 66 then complete else null end) as c66, max(case when e.course_id = 77 then complete else null end) as c77 from student as s left join enrollment as e on s.id = e.student_id group by s.id 

@Chris. Usando el procedimiento almacenado, incluso podría crear una tabla dinámica dinámica sin conocer antes el número de columnas. Este es el enlace

http://forum.html.it/forum/showthread.php?s=&threadid=1456236

de una respuesta mía en un foro italiano a un problema similar. Hay un ejemplo completo que podría ayudarlo a comprender la lógica detrás. 🙂

EDITAR. Actualice con una VISTA DINÁMICA MYSQL

Este es mi vertedero de partida:

 /*Table structure for table `student` */ drop table if exists `student`; create table `student` ( `id` int(10) unsigned not null auto_increment, `name` varchar(50) default null, primary key (`id`) ) engine=myisam; /*Data for the table `student` */ insert into `student`(`id`,`name`) values (1,'chris'); insert into `student`(`id`,`name`) values (2,'joe'); insert into `student`(`id`,`name`) values (3,'jack'); drop table if exists enrollment; create table `enrollment` ( `enrollment_id` int(11) auto_increment primary key, `student_id` int(11) default null, `course_id` int(11) default null, `complete` varchar(50) default null ) engine=myisam auto_increment=8 default charset=latin1; /*Data for the table `enrollment` */ insert into `enrollment`(`enrollment_id`,`student_id`,`course_id`,`complete`) values (1,1,55,'true'); insert into `enrollment`(`enrollment_id`,`student_id`,`course_id`,`complete`) values (2,1,66,'true'); insert into `enrollment`(`enrollment_id`,`student_id`,`course_id`,`complete`) values (3,1,77,'true'); insert into `enrollment`(`enrollment_id`,`student_id`,`course_id`,`complete`) values (4,2,55,'true'); insert into `enrollment`(`enrollment_id`,`student_id`,`course_id`,`complete`) values (5,2,66,'false'); insert into `enrollment`(`enrollment_id`,`student_id`,`course_id`,`complete`) values (6,3,55,'false'); insert into `enrollment`(`enrollment_id`,`student_id`,`course_id`,`complete`) values (7,3,66,'true'); 

y este es el procedimiento almacenado para la vista dinámica:

 delimiter // drop procedure if exists dynamic_view// create procedure dynamic_view() begin declare finish int default 0; declare cid int; declare str varchar(10000) default "select s.id,s.name,"; declare curs cursor for select course_id from enrollment group by course_id; declare continue handler for not found set finish = 1; open curs; my_loop:loop fetch curs into cid; if finish = 1 then leave my_loop; end if; set str = concat(str, "max(case when e.course_id = ",cid," then complete else null end) as course_",cid,","); end loop; close curs; set str = substr(str,1,char_length(str)-1); set @str = concat(str," from student as s left join enrollment as e on s.id = e.student_id group by s.id"); prepare stmt from @str; execute stmt; deallocate prepare stmt; -- select str; end;// delimiter ; 

Ahora vamos a llamarlo

 mysql> call dynamic_view(); +----+-------+-----------+-----------+-----------+ | id | name | course_55 | course_66 | course_77 | +----+-------+-----------+-----------+-----------+ | 1 | chris | true | true | true | | 2 | joe | true | false | NULL | | 3 | jack | false | true | NULL | +----+-------+-----------+-----------+-----------+ 3 rows in set (0.00 sec) Query OK, 0 rows affected (0.05 sec) 

Ahora insertamos otros dos registros con dos cursos diferentes:

 insert into `enrollment`(`student_id`,`course_id`,`complete`) values (1,88,'true'); insert into `enrollment`(`student_id`,`course_id`,`complete`) values (3,99,'true'); 

y recordamos el procedimiento. Este es el resultado:

 mysql> call dynamic_view(); +----+-------+-----------+-----------+-----------+-----------+-----------+ | id | name | course_55 | course_66 | course_77 | course_88 | course_99 | +----+-------+-----------+-----------+-----------+-----------+-----------+ | 1 | chris | true | true | true | true | NULL | | 2 | joe | true | false | NULL | NULL | NULL | | 3 | jack | false | true | NULL | NULL | true | +----+-------+-----------+-----------+-----------+-----------+-----------+ 3 rows in set (0.00 sec) Query OK, 0 rows affected (0.02 sec) 

Eso es todo. 🙂