Named database column with alias not found in WHERE clause

This is your original query, formatted in a more readable format:

SELECT DISTINCT
    CONTAS.contas_id AS contas_id,
    CONTAS.contastipodetalhe AS contastipodetalhe,
    CONTAS.contas_nome AS contas_nome,
    CONTAS.contas_title AS contas_title,
    CONTAS.contas_datareferencia AS anobase,
    CONTAS.contas_datareferencia AS mesbase,
    CONTAS.contas_url AS contas_url,
    CONTAS.contas_views AS contas_views,
    CONTAS.contas_downloads AS contas_downloads,
    CONTAS.contas_date AS contas_date,
    CONTAS.contas_author AS contas_author,
    CONTAS.contas_status AS contas_status,
    CONTASTIPODETALHE.contastipodetalhe_title AS contastipodetalhe_title,
    CONTASTIPO.contastipo_title AS contastipo_title
from (
    CONTAS
    join CONTASTIPODETALHE ON CONTAS.contastipodetalhe = CONTASTIPODETALHE.contastipodetalhe_id
    join CONTASTIPO ON CONTASTIPODETALHE.contastipo = CONTASTIPO.contastipo_id
)
WHERE contas_status = 1
AND contastipodetalhe = 1
AND year(anobase) = 2016
AND month(mesbase) = 12

First, this snippet is wrong:

CONTAS.contas_datareferencia AS anobase,
CONTAS.contas_datareferencia AS mesbase

This will cause both columns to have the entire date, both anobase and mesbase , without separating the year and month. It does not matter if they are separated in the WHERE clause, since in SELECT , this is not what you select.

Your mistake is that you're assuming that he first chooses the columns with SELECT and then filters the results with WHERE . But in SQL, the opposite is true. First it filters with WHERE and then chooses the columns and applies them aliases with SELECT . Therefore, when WHERE is processed, the alias has not yet been seen.

Here is your corrected query. I use it to put aliases in the tables and also use them within the WHERE clause:

SELECT DISTINCT
    c.contas_id AS contas_id,
    c.contastipodetalhe AS contastipodetalhe,
    c.contas_nome AS contas_nome,
    c.contas_title AS contas_title,
    YEAR(c.contas_datareferencia) AS anobase,
    MONTH(c.contas_datareferencia) AS mesbase,
    c.contas_url AS contas_url,
    c.contas_views AS contas_views,
    c.contas_downloads AS contas_downloads,
    c.contas_date AS contas_date,
    c.contas_author AS contas_author,
    c.contas_status AS contas_status,
    ctd.contastipodetalhe_title AS contastipodetalhe_title,
    ct.contastipo_title AS contastipo_title
FROM Contas c
JOIN ContasTipoDetalhe ctd ON c.contastipodetalhe = ctd.contastipodetalhe_id
JOIN ContasTipo ct ON ctd.contastipo = ct.contastipo_id
WHERE c.contas_status = 1
AND c.contastipodetalhe = 1
AND YEAR(c.contas_datareferencia) = 2016
AND MONTH(c.contas_datareferencia) = 12

In this query, if you prefer, you can even change this:

    YEAR(c.contas_datareferencia) AS anobase,
    MONTH(c.contas_datareferencia) AS mesbase,

So:

    2016 AS anobase,
    12 AS mesbase,

Once you know beforehand what month and year you want.

If you want to force SELECT to occur before WHERE , use parentheses and SELECT , as shown below. However, this is often not a good idea unless you know what you are doing very well, as it tends to degrade performance if not done very carefully.

SELECT * FROM (
    SELECT DISTINCT
        c.contas_id AS contas_id,
        c.contastipodetalhe AS contastipodetalhe,
        c.contas_nome AS contas_nome,
        c.contas_title AS contas_title,
        YEAR(c.contas_datareferencia) AS anobase,
        MONTH(c.contas_datareferencia) AS mesbase,
        c.contas_url AS contas_url,
        c.contas_views AS contas_views,
        c.contas_downloads AS contas_downloads,
        c.contas_date AS contas_date,
        c.contas_author AS contas_author,
        c.contas_status AS contas_status,
        ctd.contastipodetalhe_title AS contastipodetalhe_title,
        ct.contastipo_title AS contastipo_title
    FROM Contas c
    JOIN ContasTipoDetalhe ctd ON c.contastipodetalhe = ctd.contastipodetalhe_id
    JOIN ContasTipo ct ON ctd.contastipo = ct.contastipo_id
) x
WHERE x.contas_status = 1
AND x.contastipodetalhe = 1
AND x.anobase = 2016
AND x.mesbase = 12