Not sure what you mean. On Mon, Jan 28, 2013 at 8:49 AM, Douglas Nelson <douglas.nel...@oracle.com> wrote: > two different fields?? > > Thanks Doug > > 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' > > Larry Martell wrote: > > 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' > > > -- > Oracle > Douglas C. Nelson > Advanced Customer Services Principal Advanced Support Systems Analyst 5 > Mobile - 919 760 9221 >
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql