[mythtv-users] mysql query failing after upgrade to 5.5

[John Marshall]

>     SELECT p.title,
>             p.starttime,
>     ch.name
>     FROM program AS p
>        JOIN channel AS ch ON p.chanid = ch.chanid
>        JOIN recorded AS R ON p.programid = R.programid
>
>        JOIN channel AS C ON (R.chanid = C.chanid AND C.commmethod != -2)
>        LEFT JOIN (SELECT r2.starttime, r2.programid
>                 FROM recorded AS R2
>
>                 JOIN channel AS C2 ON
>                    (R2.chanid = C2.chanid AND C2.commmethod = -2)
>                ) AS rec2 on rec2.programid = p.programid
>     WHERE p.category_type = "movie"
>        AND p.starttime > CONVERT_TZ(NOW(),"SYSTEM","__UTC")
>        AND rec2.starttime IS NULL
>
>
>     It's not clear to me why there are two joins to channel - c and ch,
>     nor whether rec2 should join to p.programid or r.programid, but
>     perhaps the general suggestion will help you out.  I also don't
>     think there needs to be a GROUP BY, since there is no aggregation.
>
>
> The second join to channel (i.e., C2) is for the subquery to pick up
> recordings that were made on comm free channels. The recording table
> doesn't have that information, hence I have to pull in that data from
> the channel table. The GROUP BY is an attempt to show only one listing
> for each combination of (title, channel.name).
> It's probably an abuse of GROUP BY but it worked at the time so I didn't
> pursue it further. Ideally it would show the earliest showing for the
> title on the channel.

Yeah, I was clear on C2, but not ch.  I think I see now that R and C are 
for what was recorded, and p and ch are for the new showing you want to 
record.  If you

     SELECT p.title, min(p.starttime), ch.name
then
     GROUP BY p.title, ch.name

You'll get the earliest showing on each channel, but a title may be 
shown on multiple channels.  If you want the earliest showing on any 
channel you'll have to get fancier.  (over/partition in sql server - not 
sure with mysql.)