[mythtv-users] SQL Query Help

[Craig Huff]

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.

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