[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