Background

When viewing data an aggregated view can sometimes be easier to read.

 

Data Model

ER-Diagram

Images

 

Data Presentation

Images

Detail

 

Summary

 

GROUP_CONCAT

MySQL offers an option for aggregating data through its GROUP_CONCAT function.

Code

SQL

create database if not exists testdb;

use testdb;

start transaction;

    drop table if exists testdb.courseSchedule;

    drop table if exists testdb.student;

    drop table if exists testdb.course;


    create table testdb.student
    (
    
          id            varchar(30) not null
          
        , lastname      varchar(60) not null
        
        , firstname     varchar(60) not null

    
        , primary key 
          (
            id
          )
    
    )
    ;
    
    create table testdb.course
    (
    
          id            int         not null
                        
        , courseID      varchar(30) not null
        
        , courseName    varchar(120) not null

        , primary key 
          (
            id
          )
          
        , unique index 
          (
            courseID
          ) 
    )
    ;
    
    
    create table testdb.courseSchedule
    (
    
          studentID        varchar(30) not null
          
        , courseRefID      int         not null
        
        , calendarYear     YEAR        not null

        , calendarQuarter  tinyint     not null
        
        , primary key 
          (
              studentID
            , courseRefID
            , calendarYear
            , calendarQuarter
          )
          
        , foreign key FK_CourseSchedule_Student
          (
            studentID
          )
          references testdb.student
          (
            id
          )
          
          
        , foreign key FK_CourseSchedule_Course
          (
            courseRefID
          )
          references testdb.course
          (
            id
          )
                  
    
    )
    ;

    insert into testdb.student
    (id, lastname, firstname)
    values
        ('4511', 'Jones', 'Mary')
      , ('4512', 'Lee', 'Brandon')

      ;

    insert into testdb.course
    (id, courseID, courseName)
    values
         (1, 'ENG001', 'English 101')
      ,  (2, 'BIO101', 'Biology 101')
      ,  (3, 'PHY201', 'Physics 201')
      ,  (4, 'CS 101', 'Programming Languages Fundamental')
      ,  (5, 'CS 201', 'Data Structures')
      ,  (6, 'CS 303', 'Networking Design')
      ,  (7, 'ENG301', 'English Analytic Thinking')
      ;
    
    insert into testdb.courseSchedule
    (
          studentID
        , courseRefID
        , calendarYear
        , calendarQuarter
    )
    values
         ('4511', 1, 2020, 4 )
      ,  ('4511', 2, 2020, 4 )
      ,  ('4511', 3, 2020, 4 )
      ;
     

    insert into testdb.courseSchedule
    (
          studentID
        , courseRefID
        , calendarYear
        , calendarQuarter
    )
    values
         ('4512', 1, 2020, 1 )
      ,  ('4512', 4, 2020, 1 )
      ,  ('4512', 5, 2020, 2 )
      ,  ('4512', 6, 2020, 3 )
      ,  ('4512', 7, 2020, 3 )    
      ;
         
    select 
               tblEmp.id
               
             , tblEmp.lastname
             
             , tblEmp.firstname
            
             , tblCS.calendarYear
             
             , tblCS.calendarQuarter
             
             , tblC.courseID

             , tblC.courseName
             
    from   testdb.student tblEmp

    inner   join  testdb.courseSchedule tblCS
    
            on tblEmp.id = tblCS.studentID
            
    inner   join  testdb.course tblC
    
            on tblCS.courseRefID = tblC.id          
             
    order by
              tblEmp.id
               
             , tblEmp.lastname
             
             , tblEmp.firstname
            
             , tblCS.calendarYear
             
             , tblCS.calendarQuarter

    ;
    


    select 
               tblEmp.id
               
             , tblEmp.lastname
             
             , tblEmp.firstname
            
             , tblCS.calendarYear
             
             , tblCS.calendarQuarter
             
             , GROUP_CONCAT(
                
                        tblC.courseID
                        
                    ORDER BY 
                        tblC.courseID
                        
                    SEPARATOR 
                        ', '
                    
                ) as 'courseID'

             , GROUP_CONCAT(
                
                        tblC.courseName
                        
                    ORDER BY 
                        tblC.courseName
                        
                    SEPARATOR 
                        ', '
                    
                ) as 'courseName'
                
    from   testdb.student tblEmp

    inner   join  testdb.courseSchedule tblCS
    
            on tblEmp.id = tblCS.studentID
            
    inner   join  testdb.course tblC
    
            on tblCS.courseRefID = tblC.id  
            
    group by
              tblEmp.id
               
             , tblEmp.lastname
             
             , tblEmp.firstname
            
             , tblCS.calendarYear
             
             , tblCS.calendarQuarter
             
    order by
              tblEmp.id
               
             , tblEmp.lastname
             
             , tblEmp.firstname
            
             , tblCS.calendarYear
             
             , tblCS.calendarQuarter
    
    ;


    drop table if exists testdb.courseSchedule;

    drop table if exists testdb.student;

    drop table if exists testdb.course;



rollback;

Source Code Control

GitHub

Gist

DanielAdeniji/MySQLGroupConcatFunction.sql
Link