Search records filtering by Count from a subconsulta

Question

I have the following problem: I have three tables and need to show the employee record only when it has 1 and only 1 dependent. And it should also have 0 and at most 1 prize record, according to the structure below, in this case only the id function 3 and 4 should be displayed:

In my query I ran Counts as below:

//total premios igual a 0 ou 1
SELECT COUNT(pre.qtd) AS total_premios
FROM premios pre
INNER JOIN funcionarios fun ON fun.id = pre.id_func
GROUP BY fun.id

//total dependentes igual a 1 e apenas 1    
SELECT COUNT(1) AS total_dependentes
FROM dependentes dep
INNER JOIN funcionarios fun ON fun.id = dep.cod_func
GROUP BY fun.id

Now if you try to use it within my query I cannot use the aliases to make the rule in Where condition as below, someone could help me?

SELECT f1.name
  (SELECT COUNT(pre.qtd)
   FROM premios pre
   INNER JOIN funcionarios fun ON fun.id = pre.id_func
   GROUP BY fun.id) AS total_premios,

  (SELECT COUNT(1)
   FROM dependentes dep
   INNER JOIN funcionarios fun ON fun.id = dep.cod_func
   GROUP BY fun.id) AS total_dependentes
FROM funcionarios f1

//A CONDICAO WHERE EU NAO CONSEGUI FAZER FUNCIONAR PQ NAO RECONHECE OS CAMPOS
WHERE total_dependentes = 1  AND (total_premios = 0  OR total_premios = 1)

Demo: Sqlfiddle

Answer 1

Hello alive as you did not share your example in Sqlfiddle, I share a possible sollution but without testing in your example:

SELECT * FROM (
SELECT f1.name, 
  (SELECT COUNT(pre.qtd)
                       FROM premios pre
                       INNER JOIN funcionarios fun ON fun.id = pre.id_func
                    ) AS total_premios,
  (SELECT COUNT(1)
                       FROM dependentes dep
                       INNER JOIN funcionarios fun ON fun.id = dep.cod_func) AS total_dependentes
FROM funcionarios f1
GROUP BY f1.name) tb
WHERE total_dependentes = 1 AND (total_premios = 0  OR total_premios = 1)