Problem with WHERE and BETWEEN between two MySQL tables

Navigation
0

I'm doing a select based on a filter that I get from a form, but it falls into a problem I do not know how to solve.

I have two tables, the user table and the user_aula table. I need to count the total classes that the user gave and then do a BETWEEN in a range that I'm going to get from this filter, how do I do that? The error I have in return is that the column 'classes_data' 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'
    
asked by anonymous 29.04.2016 / 17:29

1 answer

3

Subquery solves this easy: 

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 with analytic functions of a DB, but I confess I do not know MySQL.

    
29.04.2016 / 17:45
Display content (only once) after X replicates in PHP How to transform a while structure in for? And vice versa? [closed]