MariaDB/MySQL Film data show as daily schedule - 4 time periods

Question

I have a table of films with showtime read into MySQL from a web feed. The format of the table is:

**id  film             date         time**
1    Lady Bird       2017-03-27   13:40:00
2    I, Tonya        2017-03-27   15:40:00
3    I, Tonya        2017-03-27   17:00:00
4    Lady Bird       2017-03-27   20:00:00
......

I need to format it so that there are 4 time periods (before 14:00, 14:00- 17:00,17:00-19:00, after 19:00). So the resulting data will be:

film      morning     afternoon     evening    night
Lady Bird   13:40                    17:00
I, Tonya                15:40                   20:00

I have used the following query that sets the time in columns:

SELECT 
     film,
     CASE WHEN time < "14:00" THEN time END AS morning, 
     CASE WHEN time BETWEEN "14:00" AND "17:00" THEN time END AS afternoon,
     CASE WHEN time BETWEEN "17:00" AND "19:00" THEN time END AS evening,
     CASE WHEN time >= "19:00" THEN time END AS night 
FROM 
     `films` 
WHERE 
     date=date(now())

However, the result is a separate record for each time:

Film         morning      afternoon    evening     night
Lady Bird    13:40          null         null       null
I, Tonya     null          15:40         null       null
I, Tonya     null           null        17:00       null
Lady Bird    null           null         null      20:00

Is there a way so that each film record can be combined into one with the time in the right position:

Lady Bird    13:40        null       17:00      null

If I Group By name it just shows the first time for each film discarding the rest

Answer 1

Yes, aggregate by film, and take the max of your current CASE expressions. This will collapse the several records down into a single record for each film.

SELECT 
    film,
    MAX(CASE WHEN time < "14:00" THEN time END) AS morning, 
    MAX(CASE WHEN time BETWEEN "14:00" AND "17:00" THEN time END) AS afternoon,
    MAX(CASE WHEN time BETWEEN "17:00" AND "19:00" THEN time END) AS evening,
    MAX(CASE WHEN time >= "19:00" THEN time END) AS night 
FROM films
WHERE
    date = CURDATE()
GROUP BY
    film;