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

Tab Alleman Tab.Alleman at MetroGuide.com
Tue May 11 12:40:54 CDT 2004


Thomas Klepl wrote:

> 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. 

Uh-huh, depending on your reply I was going to ask if there was a
"SegmentID" field in there somewhere, and a ShowDuration (ok,
"TotalDuration") field as well...if they weren't there, my next
suggestion would have been that you slap the DB Architect...hard.

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

As for a generic SQL book, I have no idea.  The only books I have are
MS-SQL specific.  

If I had to just pick one of those, I'd recommend WROX's Profession SQL
Server Programming by Robert Vieira

> -----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



More information about the thelist mailing list