[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