[mythtv] Joining on a calculated column

Jeremiah Morris jm at whpress.com
Mon Jan 10 21:28:54 EST 2005


On 10 Jan 2005, at 9:12 PM, David Engel wrote:

>   SELECT record.title, program.starttime,
>     TO_DAYS(program.starttime) AS progdays
>   FROM record
>   LEFT JOIN program ON record.title = program.title
>     AND progdays = record.type
>   ORDER BY program.title, program.starttime;
>
> It results in the following error message:
>
>   ERROR 1054: Unknown column 'progdays' in 'on clause'

You have to calculate the result within the join condition. It's not 
going to look at the result columns until it knows which tables to look 
in, so it parses the join information before parsing your alias in the 
column list.  Change it to:

... AND TO_DAYS(program.starttime) = record.type ...

which works in MySQL 4.0.22, at least.

- Jer



More information about the mythtv-dev mailing list