From: Larry Martell
Date: January 28 2013 4:00pm
Subject: Re: adding column breaks query
List-Archive: http://lists.mysql.com/mysql/228887
Message-Id: <CACwCsY7QFJxcx+kZbnJDnyw7wkZ6C6BXTH4AwSid14bqvqdcfA@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8

Not sure what you mean.

On Mon, Jan 28, 2013 at 8:49 AM, Douglas Nelson
<douglas.nelson@stripped> 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
>