[mythtv-users] SQL Query Help

[David Engel]

On Wed, Jan 17, 2018 at 02:30:35PM -0600, Craig Huff 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.

The full schedule can't be read directly from the database.  There's a
lot of algorithmic processing that is done and the end result is only
kept in memory on the master backend.  However, you can approximate
the schedule by starting with the oldrecorded table (its historical
name does not reflect its full and current use).  Then, join on
channel (oldrecorded.station = channel.callsign).  Finally, join on
program (oldrecorded.starttime = program.starttime and channel.chanid
= program.chanid).

BTW, this dicsussion probably belongs on the mythtv-dev mailing list.

David
-- 
David Engel
david at istwok.net