[mythtv] Joining on a calculated column

[mythtv at donoghue.org]

Use the calculation instead of the column alias in your JOIN clause:

SELECT record.title, program.starttime,
     TO_DAYS(program.starttime) AS progdays
   FROM record
   LEFT JOIN program ON record.title = program.title
     AND TO_DAYS(program.starttime) = record.type
   ORDER BY program.title, program.starttime;

 - J

> I'm working on a new Myth feature and have run into a problem
> (probably operator error) with mysql.  The following query is a
> greatly simplified example of whay I'm trying to do.
>
>   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'
>
> Basically, I'm trying to use a calculated column in a JOIN condition.
> Can someone tell me if this is allowed, and if not, how I can work
> around it.
>
> David
> --
> David Engel
> gigem at comcast.net
> _______________________________________________
> mythtv-dev mailing list
> mythtv-dev at mythtv.org
> http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-dev
>