List:General Discussion« Previous MessageNext Message »
From:Larry Martell Date:January 28 2013 3:42pm
Subject:adding column breaks query
View as plain text  
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.
Thread
adding column breaks queryLarry Martell28 Jan
  • Re: adding column breaks queryShawn Green28 Jan
    • Re: adding column breaks queryLarry Martell28 Jan
Re: adding column breaks queryLarry Martell28 Jan