[mythtv-users] Duplicate detection

[Jan Ceuleers]

On 24/09/16 10:17, Jan Ceuleers wrote:
> Note that in my tests I was unable to get the LEFT OUTER JOIN to work if
> the FROM clause contains more than one table (it said that program.title
> was unknown in the ON clause shown above). So in my mysql cli tests I
> had to drop the restriction on channel.callsign by taking channel out of
> the FROM clause and of course dropping the corresponding conditions from
> the WHERE clause. The following query works:
> 
> SELECT program.title FROM program
>   LEFT OUTER JOIN oldrecorded
>   ON (program.title = oldrecorded.title AND oldrecorded.subtitle =
> program.subtitle AND oldrecorded.recstatus IN (-3, 11))
>   WHERE program.category in
> ('film','movie','action','romance','crime','comedy','western','drama','tv
> movie','mystery','historical/period drama','literary adaptation', 'war',
> 'musical', 'crime drama') AND TIMESTAMPDIFF(MINUTE, program.starttime,
> program.endtime) >= 70 AND program.airdate >= 1970 AND
> oldrecorded.recstatus IS NULL;

Just to add to that:

Simply by adding ",channel" to the FROM clause in the above query mysql
produces the following error (and nothing else):

ERROR 1054 (42S22): Unknown column 'program.title' in 'on clause'

Mysql version is 5.5.52-0ubuntu0.12.04.1 (Ubuntu).

Jan