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
