4
Gentlemen , will searched for days the answers to this doubt that I think is silly , but I could not.
I created a view that has Matricula | Name |Job |Shift | Centrocusto | 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 code training within the IN. What I need is for you to somehow just bring in the employees who have them all and not each other like this.
view has informing table trai_func Join table functionsJoin func_area
Follow the code of worked:
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
Cara o in really works this way, it brings any item of the list of IN bata... It is not feasible to do a
SELECT * FROM vw_treina_func TF WHERE TF.CodTreina = 1 and TF.CodTreina = 16 and TF.CodTreina = 22 and TF.CodTreina = 33 and TF.CodTreina = 75 and TF.CodTreina = 97110 and TF.CodTreina = 115 and TF.CodTreina = 158 and TF.CodTreina = 161 and TF.CodTreina = 164 and TF.CodTreina = 173
– Jhonatan Jorge de Lima
thank you, people I am testing all the alternatives this with and I tried the example I reported in this coming from a select , same deal in php making a loop to generate all the and brings no result no record and know that in the reported example is to bring back 30 employees. I made this select directly in the bank and still does not bring result with and.
– Luciano Silva