On Mon, Jan 28, 2013 at 8:56 AM, Shawn Green <shawn.l.gr...@oracle.com> wrote:
> Hello Larry,
>
>
> On 1/28/2013 10:42 AM, Larry Martell wrote:
>>
>> I have a query that works fine:
>>
>>
>> SELECT data_ppl.name as 'P/P/L', data_recipe.name as Recipe,
>> data_cstmeta.date_time as 'Last Run'
>> FROM data_recipe, data_ppl, data_cstmeta
>> INNER JOIN (SELECT id,recipe_id, MAX(date_time) as MaxDateTime
>> FROM data_cstmeta
>> GROUP BY recipe_id) grouped
>> ON data_cstmeta.id = grouped.id
>> AND data_cstmeta.date_time = grouped.MaxDateTime
>> WHERE data_cstmeta.ppl_id = data_ppl.id
>> AND data_cstmeta.recipe_id = data_recipe.id
>> ORDER BY data_cstmeta.date_time desc;
>>
>>
>> Now I need to add one more column to it, data_tool.name, so I did this:
>>
>>
>> SELECT data_ppl.name as 'P/P/L', data_tool.name as Tool,
>> data_recipe.name as Recipe, data_cstmeta.date_time as 'Last Run'
>> FROM data_recipe, data_ppl, data_cstmeta, data_tool
>> INNER JOIN (SELECT id,tool_id,recipe_id, MAX(date_time) as MaxDateTime
>> FROM data_cstmeta
>> GROUP BY recipe_id) grouped
>> ON data_cstmeta.id = grouped.id
>> AND data_cstmeta.date_time = grouped.MaxDateTime
>> WHERE data_cstmeta.ppl_id = data_ppl.id
>> AND data_cstmeta.recipe_id = data_recipe.id
>> AND data_cstmeta.tool_id = data_tool.id
>> ORDER BY data_cstmeta.date_time desc;
>>
>> And now it fails with:
>>
>> ERROR 1054 (42S22): Unknown column 'data_cstmeta.id' in 'on clause'
>>
>> I've messed around with this for a day and I can't figure out what
>> stupid mistake I'm making.
>>
>
> You didn't add just one column, you added an entire table to the FROM
> clause. Also, you used a comma-join instead of an ANSI JOIN clause.
>
> Because of changes we made in 5.0.12 to make MySQL behave more like the SQL
> standard requires, the priority of the comma operator in join operations was
> demoted. This means that when the explicit JOIN declaration is being
> evaluated (which has a higher order of precedence) only the derived table
> (grouped) and the base table `data_tool` are visible to the ON clause. The
> `data_cstmeta` implicit join will be evaluated later as it is based on a
> comma-join.
>
> I suggest you rewrite your queries to only use the explicit "JOIN ... ON
> ..." syntax for all of your joins to avoid these problems in the future.
> http://dev.mysql.com/doc/refman/5.5/en/join.html
>
> Regards,
> --
> Shawn Green
> MySQL Principal Technical Support Engineer
> Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
> Office: Blountville, TN
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql
>
Thanks. I know the JOIN clause is preferred over the comma join, just
surprised this query worked until I added a new table. In any case I
changed it as shown below, and it works now. Thanks!
SELECT data_ppl.name as 'P/P/L',
data_tool.name as Tool,
data_recipe.name as Recipe,
data_cstmeta.date_time as 'Last Run'
FROM data_cstmeta
INNER JOIN (SELECT id,tool_id,recipe_id, MAX(date_time) as MaxDateTime
FROM data_cstmeta
GROUP BY recipe_id) grouped
ON data_cstmeta.id = grouped.id AND data_cstmeta.date_time =
grouped.MaxDateTime
INNER JOIN data_ppl ON data_cstmeta.ppl_id = data_ppl.id
INNER JOIN data_tool ON data_cstmeta.tool_id = data_tool.id
INNER JOIN data_recipe ON data_cstmeta.recipe_id = data_recipe.id
ORDER BY data_cstmeta.date_time desc;
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
