[mythtv-users] SQL Query Help

[jrh]

> On Jan 17, 2018, at 3:30 PM, Craig Huff <huffcslists at gmail.com> wrote:
> 
> Long story short: Trying to compose a query to list upcoming recordings of a particular show (mainly for curiosity, but also to knock the rust off my SQL knowledge), but I'm at an impasse.
> 
> I can see the shows that are upcoming with:
> select recgroup, program.seriesid, program.programid, syndicatedepisodenumber, program.starttime, program.title, program.subtitle from program join record where program.seriesid=record.seriesid and program.title='Gunsmoke' order by program.starttime;
> 
> and I can see the recording rule(s) with:
> select recgroup, seriesid, programid, startdate, title, subtitle from record where title='Gunsmoke' order by startdate ;
> 
> but I haven't figured out how to show only the upcoming shows that will be recorded.  I was trying to at least exclude those that had already been recorded by doing an outer join against the results of the first query above where recorded.programid=program.programid, but I can't figure out (forgot or there's a syntax trick missing) to take the first query's results as a temporary table to do the join against the recorded table.
> 
> Variations on the following don't work:
> select recgroup, program.seriesid, program.programid, syndicatedepisodenumber, program.starttime, program.title, program.subtitle from ( program join record where program.seriesid=record.seriesid and program.title='Gunsmoke' order by program.starttime ) outer join recorded where recorded.programid=record.programid ;
> 
> Any help would be appreciated.
> 

I came up with the following:

SELECT * FROM `oldrecorded` where endtime >= '2018-01-17 20:00:00' and recstatus = -1  
 ORDER BY `oldrecorded`.`starttime`  ASC

You will want to modify the endtime to reflect the time frame desired. NOTE that endtime is UTC time and NOT localtime.

regards,

Jay

> --
> Craig.
> _______________________________________________
> mythtv-users mailing list
> mythtv-users at mythtv.org
> http://lists.mythtv.org/mailman/listinfo/mythtv-users
> http://wiki.mythtv.org/Mailing_List_etiquette
> MythTV Forums: https://forum.mythtv.org

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.mythtv.org/pipermail/mythtv-users/attachments/20180117/9ef54052/attachment.html>