[thelist] mySQL - expert advice needed for TV schedule query

[Thomas Klepl]

Hi all,
 
I just joined this list hoping to learn something from the SQL experts out
there and possibly offer some of my expertise in Flash if those topics come
up.
 
Anyways, I have a query which grabs data from two tables of a mySQL database
holding information about scheduling of TV programs. The database is read
only, so I can't add or change anything. There are two tables I'm using:

1) 'schedule' - holds information about when a program is to appear
2) 'content_detail' - holds details about each program in the schedule

The query returns a schedule for a 24 hour period, starting at 6:00 AM and
displays the title of the program, the start time, and the end time. The two
tables are joined on a key called 'house_number_full'.

The way TV programs are recorded into the database is a little tricky. Each
record in the schedule has a segment ID. For shorter programs there is a
single record with a segment ID of '01'. A longer program could have, say, 4
records with segment IDs of '01' thru '04'. My query is designed to return
the start time of the first segment, and the end time of the last segment,
and display this as a single row (so that the user does not know about the
segments and just sees the start and end time for the whole program). The
reason there are segments is that TV ads appear between the segments.

This is all fine up to here.

A twist to the whole scenario is that a program could be scheduled more than
once within a 24 hour period. These entries cannot be differentiated from
each other except the fact that they are scheduled several hours apart and
do not belong together. The problem is that the query thinks the two
showings of the one program are actually one showing, resulting in a start
time and end time which are very far apart. I need to somehow have
'sub-groups' or something to cause the query to spit out a separate row for
each showing of a program. Keep in mind that each program can have any
number of segments which must be aggregated.

My query as it stands is below:
(You can ignore the $identifier and $contentType variables - these are just
filters).

-----

SELECT schedule.house_number_full, schedule.date, MIN(schedule.start_time)
AS start_time, MAX(schedule.end_time) AS end_time, content_detail.title

FROM schedule

LEFT JOIN content_detail ON content_detail.house_number_full =
schedule.house_number_full

WHERE schedule.identifier LIKE '$identifier'

AND schedule.house_number_prefix LIKE '$contentType'

AND (((schedule.date LIKE '$startDate') AND (schedule.start_time >=
$startTime))

OR ((schedule.date LIKE '$endDate') AND (schedule.start_time <
$startTime_tomorrow)))

GROUP BY schedule.house_number_full, schedule.date, content_detail.title

ORDER BY schedule.date, schedule.start_time

-----

Ideally I'd like to take care of this in one query. But I'm open to other
ways of getting the desired result.
I'm using PHP.


Thanks in advance,
Tom