<html><head><meta http-equiv="Content-Type" content="text/html; charset=us-ascii"></head><body style="word-wrap: break-word; -webkit-nbsp-mode: space; line-break: after-white-space;" class=""><br class=""><div><br class=""><blockquote type="cite" class=""><div class="">On Jan 17, 2018, at 3:30 PM, Craig Huff <<a href="mailto:huffcslists@gmail.com" class="">huffcslists@gmail.com</a>> wrote:</div><br class="Apple-interchange-newline"><div class=""><div dir="ltr" class=""><div class=""><div class=""><div class=""><div class="">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.<br class=""><br class=""></div>I can see the shows that are upcoming with:<br class=""><div style="margin-left:40px" class="">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;<br class=""></div><br class="">and I can see the recording rule(s) with:<br class=""><div style="margin-left:40px" class="">select recgroup, seriesid, programid, startdate, title, subtitle from record where title='Gunsmoke' order by startdate ;<br class=""></div><br class=""></div>but I haven't figured out how to show only the upcoming shows that <i class=""><u class=""><b class="">will</b></u></i> 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.<br class=""><br class=""></div><div class="">Variations on the following don't work:<br class=""><div style="margin-left:40px" class="">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 ;<br class=""></div><br class=""></div>Any help would be appreciated.<br class=""><br class=""></div></div></div></blockquote><div><br class=""></div>I came up with the following:</div><div><br class=""></div><div><div>SELECT * FROM `oldrecorded` where endtime >= '2018-01-17 20:00:00' and recstatus = -1 </div><div> ORDER BY `oldrecorded`.`starttime` ASC</div><div><br class=""></div><div>You will want to modify the endtime to reflect the time frame desired. NOTE that endtime is UTC time and NOT localtime.</div><div><br class=""></div><div>regards,</div><div><br class=""></div><div>Jay</div><div><br class=""></div><blockquote type="cite" class=""><div class=""><div dir="ltr" class=""><div class="">--<br class=""></div>Craig.<br class=""></div>
_______________________________________________<br class="">mythtv-users mailing list<br class=""><a href="mailto:mythtv-users@mythtv.org" class="">mythtv-users@mythtv.org</a><br class="">http://lists.mythtv.org/mailman/listinfo/mythtv-users<br class="">http://wiki.mythtv.org/Mailing_List_etiquette<br class="">MythTV Forums: https://forum.mythtv.org<br class=""></div></blockquote></div><br class=""></body></html>