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

Karl Newman newmank1 at asme.org
Thu Aug 14 19:42:36 UTC 2014


On Thu, Aug 14, 2014 at 12:11 PM, John Marshall <mythtv at marshallparty.org>
wrote:

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

That's what I want, the earliest showing on each channel, even if the title
is on multiple channels (because one of the channels may be the one I
want). Oh, by the way, the USING(column) is a shorthand for ON
(table1.column = table2.column). If I'm going to use a nested select, then
I could probably get rid of the left join altogether. I was trying to be
tricky/optimize it. Originally I wanted to write this as a power query to
automatically re-record movies on comm free channels as they aired, but the
power queries are not actually that powerful--mythweb stripped out some key
punctuation, and the frontend made me discover that the query length is
limited to 128 characters or something and I was just over that.

Thanks,

Karl
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.mythtv.org/pipermail/mythtv-users/attachments/20140814/ca8fa5d8/attachment.html>


More information about the mythtv-users mailing list