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?

Answer 1

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;