Problem with WHERE and BETWEEN two Mysql tables

Question

I’m making a select based on a filter I receive from a form, but it falls into a problem I don’t know how to solve.

I have two tables, the user table and the user_aula table. I need to count the total of lessons that the user gave and then do a BETWEEN in a range that I will receive from this filter, how to do this? The error I have as return is that the column 'aulas_given' does not exist.

SELECT user.*, 
(SELECT count(*) FROM user_aula WHERE user_aula_prof = user.user_id AND user_aula_status IN (1, 2, 3)) as aulas_dadas, 
(SELECT count(*) FROM user_aula WHERE user_aula_aluno = user.user_id AND user_aula_status IN (1,2, 3)) as aulas_feitas 
FROM user 
WHERE user_nome_completo LIKE '%%' 
AND user_email LIKE '%%'
AND user_ref LIKE '%%'
AND user_login BETWEEN '0' AND '9999'
AND aulas_dadas BETWEEN '0' AND '9999'

Answer 1

With subquery that is easily solved:

SELECT T.* FROM (
SELECT user.*, 
(SELECT count(*) FROM user_aula WHERE user_aula_prof = user.user_id AND user_aula_status IN (1, 2, 3)) as aulas_dadas, 
(SELECT count(*) FROM user_aula WHERE user_aula_aluno = user.user_id AND user_aula_status IN (1,2, 3)) as aulas_feitas 
FROM user 
WHERE user_nome_completo LIKE '%%' 
AND user_email LIKE '%%'
AND user_ref LIKE '%%') T
WHERE T.aulas_feitas BETWEEN 0 AND 9999
AND T.aulas_dadas BETWEEN 0 AND 9999

So you create filters in the result of your main query. I like to work more with analytic functions of a comic book, but I confess that I do not know the ones of Mysql.