[mythtv-users] Help creating an SQL query from multiple tables

[George Nassas]

On 23-May-06, at 11:47 PM, Michael Rice wrote:

> The following is a query I use as part of a script I am writing to 
> check for new movies:
>
> select distinct title,description,airdate,stars*4 from program where 
> airdate >= 2005 and category_type = 'movie' order by title;
>
> It works ok but I'd like to exclude any titles that I've recorded 
> before.  As I understand it that info is in two other tables 
> (recorded, oldrecorded) and I don't exactly know how to join them.

Subqueries with not exists are handy:

select distinct title,description,airdate,stars*4 from program where 
airdate >= 2005 and category_type = 'movie'
and not exists (select * from oldrecorded where oldrecorded.programid = 
program.programid)
and not exists (select * from recorded where recorded.programid = 
program.programid)
order by title;

I chose programid only for brevity, you can match on category and title 
if you wish. If you do you should put indexes on category + title since 
not exists has to do a full table scan without them. Programid is 
already indexed in those three tables.

Also, you could change the 2005 to be (year(curdate()) - 1) and save 
yourself a yearly code tweak.

- George