0

I have a table which contains TV Guide data.

In a simplified form, the columns look like the following...

_id, title, start_time, end_time, channel_id

What I'm trying to do is create a list of TV shows in a NOW/NEXT format. Generating the 'NOW' list (what's currently being broadcast) is easy but trying to get a list of what is showing 'NEXT' is causing me problems.

I tried this...

SELECT * from TV_GUIDE where start_time >= datetime('now') GROUP BY channel_id

Sure enough this gives me one TV show for each TV channel_id but it gives me the very last shows (by date/time) in the TV_GUIDE table.

SQL isn't my strong point and I'm struggling to work out why only the last TV shows are returned. It seems I need to do a sub-query of a query (or a query of a sub-query). I've tried combinations of ORDER BY and LIMIT but they don't help.

Squonk
  • 48,735
  • 19
  • 103
  • 135
  • You shouldn't select columns that don't appear in the group by clause unless they are in aggregate functions. You should also avoid `SELECT *`. – Mark Byers Jun 24 '12 at 00:50

1 Answers1

1

I believe that you first must select the couples (channel_id, ) from TV_GUIDE, based on your criteria. Then, you will display those records of TV_GUIDE which match those criteria:

SELECT source.* from TV_GUIDE AS source
JOIN (SELECT channel_id, MIN(start_time) AS start_time
    FROM TV_GUIDE
    WHERE start_time >=  now() GROUP BY channel_id ) AS start_times
ON (source.channel_id = start_times.channel_id 
    AND source.start_time = start_times.start_time)
ORDER BY channel_id;

This first selects all shows with minimum start time, one for each channel, thus giving you the channel id and the start time. Then fills in the other information (MySQL sometimes lets you retrieve that from a single query, but I feel it's a bad habit to acquire - maybe you add a field and it won't work anymore) from a JOIN with the same table.

You might want to add an index on the combined fields (start_time, channel_id). Just to be on the safe side, make it a UNIQUE index.

LSerni
  • 55,617
  • 10
  • 65
  • 107
  • +1 I think your answer may be right but I need to double check to make sure all of the data is being returned correctly. It's late here now so I'll try later but first tests show it is working. Many thanks. – Squonk Jun 24 '12 at 05:03