mysql - Join two tables (with a 1-M relationship) where the second table needs to be 'flattened' into one row -

given following tables:

student

+----+-------+ | id | name  | +----+-------+ | 1  | chris | | 2  | joe   | | 3  | jack  | +----+-------+ 

enrollment

+---------------+------------+-----------+----------+ | 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     | +---------------+------------+-----------+----------+ 

i following

+----+-------+-----------+-----------+-----------+ | id | name  | course 55 | course 66 | course 77 | +----+-------+-----------+-----------+-----------+ | 1  | chris | true      | true      | true      | | 2  | joe   | true      | false     | null      | | 3  | jack  | false     | true      | null      | +----+-------+-----------+-----------+-----------+ 

note 1: know mysql can't have dynamic columns (correct me if i'm wrong!) happy query starting as:

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

i happy because there fixed number of courses (4 exact). ideally want dynamic; is, not having manually write each course in select clause.

note 2: needs mysql pure - don't want resort php.

the database stands @ 10000+ students 10000+ * 4 enrollments (as there 4 courses, , every student in 4 modules).

note 3: student.user_id indexed , enrollment.enrollment_id, enrollment.student_id, , enrollment.course_id.

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

@chris. using stored procedure create dynamic pivot table without knowing before number of columns. link

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

of answer of mine on italian forum similar problem. there complete example understand logic behind. :)

edit. update mysql dynamic view

this starting dump:

/*table structure 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 table `student` */  insert  `student`(`id`,`name`) values (1,'chris'); insert  `student`(`id`,`name`) values (2,'joe'); insert  `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 table `enrollment` */  insert  `enrollment`(`enrollment_id`,`student_id`,`course_id`,`complete`) values (1,1,55,'true'); insert  `enrollment`(`enrollment_id`,`student_id`,`course_id`,`complete`) values (2,1,66,'true'); insert  `enrollment`(`enrollment_id`,`student_id`,`course_id`,`complete`) values (3,1,77,'true'); insert  `enrollment`(`enrollment_id`,`student_id`,`course_id`,`complete`) values (4,2,55,'true'); insert  `enrollment`(`enrollment_id`,`student_id`,`course_id`,`complete`) values (5,2,66,'false'); insert  `enrollment`(`enrollment_id`,`student_id`,`course_id`,`complete`) values (6,3,55,'false'); insert  `enrollment`(`enrollment_id`,`student_id`,`course_id`,`complete`) values (7,3,66,'true'); 

and stored procedure dynamic view:

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 select course_id enrollment group course_id; declare continue handler not found set finish = 1; open curs; my_loop:loop fetch curs cid; if finish = 1 leave my_loop; end if; set str = concat(str, "max(case when e.course_id = ",cid," complete else null end) course_",cid,","); end loop; close curs; set str = substr(str,1,char_length(str)-1); set @str = concat(str," student s             left join enrollment e             on s.id = e.student_id             group s.id"); prepare stmt @str; execute stmt; deallocate prepare stmt; -- select str; end;// delimiter ; 

now let's call it

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) 

now insert other 2 records 2 different courses:

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

and recall procedure. result:

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) 

that's all. :)