Sirs, I've been looking for days to answer this question, which I think is silly, but I could not.
I created a view that brings registration | Name | Title | Turn | About Us | CodTreina | Status
I made this select:
SELECT * FROM vw_treina_func TF WHERE TF.CodTreina IN (1,16,22,33,75,97110, 115, 158,161,164,173,)
This select is bringing employees who have one or other training of the code within the IN. What I need is that somehow it just brings employees who have all and not each other like this happening.
the view has information of the table treina_func join table officials join func_area
Follow the right code:
SELECT a.Codigo, a.descricao, fa.CentroCusto, f.MatriculaSese,r.Descricao, f.nome
FROM area a
INNER JOIN treina_area ta ON ta.CodArea = a.Codigo
INNER JOIN treina_func tf ON tf.CodTreina = ta.CodTreina
INNER JOIN funcionarios f ON f.MatriculaSese = tf.MatriculaSese
INNER JOIN func_area fa ON f.MatriculaSese = fa.MatriculaSese
INNER JOIN area r ON r.Codigo = fa.CentroCusto
LEFT JOIN ( SELECT CodArea, Codtreina, (SELECT COUNT(DISTINCT iita.CodTreina) FROM treina_area iita WHERE iita.CodArea = ita.CodArea) numTreinos FROM treina_area ita ) TodosTreinosArea ON TodosTreinosArea.CodArea = ta.CodArea AND TodosTreinosArea.Codtreina = ta.CodTreina
WHERE a.Codigo = 2 and fa.CentroCusto <> 2 GROUP BY a.descricao, f.MatriculaSese, f.nome
HAVING COUNT(DISTINCT tf.CodTreina) = COUNT(CASE WHEN TodosTreinosArea.CodTreina IS NOT NULL THEN TodosTreinosArea.CodTreina END) AND COUNT(DISTINCT tf.CodTreina) = MAX(TodosTreinosArea.numTreinos) ORDER BY 1, 2, 3