SELECT foo AS bar not working

Question

My code:

SELECT pulpi_juegos.*, mapa.cat_id AS IDcategoria 
FROM pulpi_juegos 
INNER JOIN pulpi_categorias_mapa AS mapa ON pulpi_juegos.id=mapa.game_id 
WHERE pulpi_juegos.Estado != 1 
AND IDcategoria = 15 
ORDER BY pulpi_juegos.id DESC 
LIMIT 0,30;

Trouble is I'm getting the following error:

Unknown column 'IDcategoria' in 'where clause'

Anyone know why?

[SELECT: order of processing](http://stackoverflow.com/questions/6545664/using-case-expression-column-in-where-clause/6545685#6545685) — ypercubeᵀᴹ, May 02 '12 at 09:29

score 2 · Answer 1 · answered May 02 '12 at 09:21

use mapa.cat_id instead of IDCategoria, because mysql executes the SELECT later.

SELECT pulpi_juegos.*, mapa.cat_id AS IDcategoria 
FROM pulpi_juegos 
INNER JOIN pulpi_categorias_mapa AS mapa ON pulpi_juegos.id=mapa.game_id 
WHERE pulpi_juegos.Estado != 1 
AND mapa.cat_id = 15 
ORDER BY pulpi_juegos.id DESC 
LIMIT 0,30;

like this order

FROM clause
WHERE clause
GROUP BY clause
HAVING clause
SELECT clause
ORDER BY clause

score 1 · Answer 2 · answered May 02 '12 at 09:22

you can't put an alias in WHERE clause.

Use this:

SELECT pulpi_juegos.*, mapa.cat_id AS IDcategoria 
FROM pulpi_juegos 
INNER JOIN pulpi_categorias_mapa AS mapa ON pulpi_juegos.id=mapa.game_id 
WHERE pulpi_juegos.Estado != 1 
AND mapa.cat_id = 15 
ORDER BY pulpi_juegos.id DESC 
LIMIT 0,30;

Good luck.

Rick Hoving · Accepted Answer · 2012-05-02T11:28:03.843

SQL always executes the SELECT statement later then your WHERE statement. Correct me if I'm wrong but SQL executes your statement as follows:

FROM
INNER JOIN
WHERE
AND
SELECT
ORDER BY

So as you can see now, when SQL comes to your AND statement, it does not know yet that mapa.cat_id is actually represented as IDcategoria. A simple solution would be changing the IDcategoria in your AND to mapa.cat_id.
So your query will be the following:

SELECT pulpi_juegos.*, mapa.cat_id AS IDcategoria 
FROM pulpi_juegos 
INNER JOIN pulpi_categorias_mapa AS mapa ON pulpi_juegos.id=mapa.game_id 
WHERE pulpi_juegos.Estado != 1 
AND mapa.cat_id = 15 
ORDER BY pulpi_juegos.id DESC 
LIMIT 0,30;

`FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY` – ypercubeᵀᴹ May 02 '12 at 09:26 — ypercubeᵀᴹ, May 02 '12 at 09:26

score 1 · Answer 4 · answered May 02 '12 at 09:22

You can not use alias in where clause , instead you should write the column name . If want to use alias in where you have to use sub query like this

SELECT * FROM (SELECT pulpi_juegos.*, mapa.cat_id AS IDcategoria 
FROM pulpi_juegos 
INNER JOIN pulpi_categorias_mapa AS mapa ON pulpi_juegos.id=mapa.game_id 
WHERE pulpi_juegos.Estado != 1  
ORDER BY pulpi_juegos.id DESC 
LIMIT 0,30) WHERE IDcategoria = 15 ;

now it will work.

SELECT foo AS bar not working

4 Answers4