This is your original query, formatted in a more readable way:
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, that stretch is wrong:
CONTAS.contas_datareferencia AS anobase,
CONTAS.contas_datareferencia AS mesbase
This will make both columns have the entire date, both the anobase and the mesbase, without separating the year and the month. It does not matter if they are separated in the clause WHERE, since in the SELECT, that’s not what you select.
Your mistake is that you are assuming that first he chooses the columns with the SELECT and then filter the results with the WHERE. But in SQL, what happens is the opposite. First it filters with the WHERE and then choose the columns and apply aliases to them with the SELECT. Thus, when the WHERE is processed, alias not seen yet.
Here is your query corrected. I take the opportunity to put aliases in the tables and also use them within the clause WHERE:
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 consultation, if you prefer, you can even change this:
YEAR(c.contas_datareferencia) AS anobase,
MONTH(c.contas_datareferencia) AS mesbase,
That’s why:
2016 AS anobase,
12 AS mesbase,
Once you know beforehand which is the month and year you want.
If you want to force the SELECT occur before the WHERE, use a parenthesis and a sub-SELECT, as it is below. However, this is usually not a good idea unless you know very well what you are doing, 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
It is not all DB that allows the use of alias in the conditions or order. Tried using the original column name?
– Bacco
cannot use the original name as I am renaming the same column 2 times.
– Jardel
I could not see where this would prevent the use of the original name. In fact, I did not see reason to rename twice, instead of using a single name. I imagine you have some, but I don’t know what it is.
– Bacco
i need to have two fields: anobase and mesbase, so I can use the Where clause like this: WHERE contas_status = 1 AND contastipodetalhe = 1 AND year(anobase) = 2016 AND Month(mesbase) = 12
– Jardel
To use so the original name would solve perfectly. But in Victor’s answer has more details of how to do right, and with alias.
– Bacco