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

Tab Alleman Tab.Alleman at MetroGuide.com
Mon May 10 08:39:33 CDT 2004


Thomas Klepl wrote:
> 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. 

I don't see anything in your query that seems to have anything to do
with segments...how does your database differentiate between a segment
of a program, and a later showing of a program?  From what I see below,
you are asking for the start time of the first showing and the end time
of the last showing.  All you would need to do AFAICS, is take out the
GROUP BY clause and the MIN/MAX functions, and you'll get a separate row
for each showing, which you can work with in php...but I don't
understand how "segments" come into play, in either your database, or
your target output.

> 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


More information about the thelist mailing list