Query to show class schedule in MySQL

Question

Query to show class schedule in MySQL

Navigation

#1 by (3 votes)

3

Good afternoon, I am trying to generate a class schedule and it took several days without being able to generate the schedule through a query in MySQL.

The query I'm doing in MySQL is the following:

SELECT CONCAT(DATE_FORMAT(h.hora_inicio,"%H:%i")," - ",DATE_FORMAT(h.hora_fin,"%h:%i")) hora,
CASE WHEN ho.dia="Lunes" THEN ho.nombre ELSE '' END Lunes,
CASE WHEN ho.dia="Martes" THEN ho.nombre ELSE '' END Martes,
CASE WHEN ho.dia="Miercoles" THEN ho.nombre ELSE '' END Miercoles,
CASE WHEN ho.dia="Jueves" THEN ho.nombre ELSE '' END Jueves,
CASE WHEN ho.dia="Viernes" THEN ho.nombre ELSE '' END Viernes,
CASE WHEN ho.dia="Sabado" THEN ho.nombre ELSE '' END Sabado FROM    (
        SELECT h.codigo_hora,h.codigo_docentes,c.nombre,h.dia 
          FROM horario h 
    INNER JOIN curso c 
            ON c.codigo_curso=h.codigo_curso
) ho 
INNER JOIN docentes d 
        ON d.codigo_docentes=ho.codigo_docentes 
INNER JOIN hora h 
        ON h.codigo_hora=ho.codigo_hora 
  GROUP BY hora 
  ORDER BY hora ASC

The result obtained when executing the query is the following:

I have several records of my courses assigned to teachers at different times but it does not show me what the class schedule should be like, in several cells it is empty and I do not know what the query would be wrong with, since I have several records. Hopefully you can help me solve this problem.

mysql sql

asked by Juan Vásquez Ventura 03.10.2016 в 23:07

source

1 answer

Error updating my remote repository in Git hub Open a java .class file in Eclipse IDE

score 3 · Accepted Answer

You need to use an aggregation function for the columns that you are not using in GROUP BY (in most database engines it is mandatory, MySQL allows it, but it gives you results like these). The code would be:

SELECT  CONCAT(DATE_FORMAT(h.hora_inicio,"%H:%i")," - ",DATE_FORMAT(h.hora_fin,"%h:%i")) hora,
        MAX(CASE WHEN ho.dia = 'Lunes' THEN ho.nombre ELSE '' END) Lunes,
        MAX(CASE WHEN ho.dia = 'Martes' THEN ho.nombre ELSE '' END) Martes,
        MAX(CASE WHEN ho.dia = 'Miercoles' THEN ho.nombre ELSE '' END) Miercoles,
        MAX(CASE WHEN ho.dia = 'Jueves' THEN ho.nombre ELSE '' END) Jueves,
        MAX(CASE WHEN ho.dia = 'Viernes' THEN ho.nombre ELSE '' END) Viernes,
        MAX(CASE WHEN ho.dia = 'Sabado' THEN ho.nombre ELSE '' END) Sabado 
FROM (  SELECT  h.codigo_hora,
                h.codigo_docentes,
                c.nombre,
                h.dia 
        FROM horario h 
        INNER JOIN curso c 
            ON c.codigo_curso = h.codigo_curso) ho 
INNER JOIN docentes d 
    ON d.codigo_docentes = ho.codigo_docentes 
INNER JOIN hora h 
    ON h.codigo_hora = ho.codigo_hora 
GROUP BY CONCAT(DATE_FORMAT(h.hora_inicio,"%H:%i")," - ",DATE_FORMAT(h.hora_fin,"%h:%i")) 
ORDER BY CONCAT(DATE_FORMAT(h.hora_inicio,"%H:%i")," - ",DATE_FORMAT(h.hora_fin,"%h:%i")) ASC;