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

[Thomas Klepl]

Thanks for the reply Tab - you're the only brave one to attempt an answer.

The database (which I did not design) does not specifically distinguish
multiple showings... they just appear later in the day :) This was the
problem, when I was trying to determine duration based on start and end
times...

I discovered today that there was a 'total duration' field in the
content_detail table, so I could easily get total durations for entire
programs. I was able to drop the GROUP BY and MIN/MAX functions and used a
WHERE clause to eliminate records with a segment ID higher than 01.

Thanks anyways.
BTW, what is considered the defacto "must have" SQL book out there (or what
would you suggest..)?

Tom


-----Original Message-----
From: thelist-bounces at lists.evolt.org
[mailto:thelist-bounces at lists.evolt.org] On Behalf Of Tab Alleman
Sent: May 10, 2004 9:40 AM
To: thelist at lists.evolt.org
Subject: RE: [thelist] mySQL - expert advice needed for TV schedule query

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