From: Larry Martell
Date: January 28 2013 3:42pm
Subject: adding column breaks query
List-Archive: http://lists.mysql.com/mysql/228885
Message-Id: <CACwCsY4v3usakgrgeOYoYZ9o76Rj-ix_eD2TKQ3HGhxRJhStDg@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8

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.