0
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
group by would not work like this, pq returns more than one record
– tkmtts
As I indicated, you did not have the example to test I did not notice a detail, I verified that you created in Sqlfiddle, that helps a lot. You do not need a group by in a subselect of a Count, now check the query I put, corrected.
– Ernesto Casanova