Select Where IN all records

Question

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

Answer 1

You can try something like this:

SELECT CodFuncionario
  FROM vw_treina_func TF 
 WHERE TF.CodTreina IN (1, 16, 22, 33, 75, 97, 110, 115, 158, 161, 164, 173)
GROUP BY CodFuncionario
HAVING COUNT(DISTINCT TF.CodTreina) = 12

The important part is the instruction: HAVING COUNT(DISTINCT TF.Codtreina) = 12 This will limit the results to employees who have 12 different Codtrains.

Edit after @Lucianosilva comment

The aim is to identify which officials have completed all training for the selected area.

As an example I created a small database with some of the tables in your model.

CREATE TABLE area
(
  cod_area  INT,
  descricao NVARCHAR(25)
);

CREATE TABLE treino_area
(
  cod_treina  INT,
  cod_area INT
);

CREATE TABLE funcionario
(
  cod_func  INT,
  nome      NVARCHAR(25)
);

CREATE TABLE treino_func
(
  cod_func   INT,
  cod_treina INT
);

Then I uploaded some test data

INSERT INTO area(cod_area, descricao)VALUES
(1, 'Area A1'),
(2, 'Area B2');

INSERT INTO treina_area(cod_treina, cod_area)VALUES
(1, 1), -- Area A1 treino 1
(2, 1), -- Area A1 treino 2
(2, 2); -- Area B2 treino 2

INSERT INTO funcionario(cod_func, nome)VALUES
(1, 'Jorge'),
(2, 'Manuel'),
(3, 'Pedro');

INSERT INTO treina_func(cod_func, cod_treina)VALUES
(1, 1), -- Jorge treino 1
(1, 2), -- Jorge treino 2
(2, 1), -- Manuel treino 1
(3, 2); -- Pedro treino 2

The following query will return the employees who have completed all training to the "Area A1 area".

SELECT a.cod_area,
       a.descricao,
       f.cod_func,
       f.nome
  FROM area a
 INNER JOIN treina_area ta
    ON ta.cod_area = a.cod_area
 INNER JOIN treina_func tf
    ON tf.cod_treina = ta.cod_treina
 INNER JOIN funcionario f
    ON f.cod_func = tf.cod_func
 LEFT JOIN 
 (
    SELECT cod_area,
           cod_treina,
           (SELECT COUNT(DISTINCT iita.cod_treina) FROM treina_area iita WHERE iita.cod_area = ita.cod_area) numTreinos
      FROM treina_area ita       
 ) TodosTreinosArea
    ON TodosTreinosArea.cod_area = ta.cod_area
   AND TodosTreinosArea.cod_treina = ta.cod_treina
 WHERE a.cod_area = 1
 GROUP BY a.descricao, f.cod_func, f.nome
 HAVING COUNT(DISTINCT tf.cod_treina) = COUNT(CASE WHEN TodosTreinosArea.cod_treina IS NOT NULL THEN TodosTreinosArea.cod_treina END)
    AND COUNT(DISTINCT tf.cod_treina) = MAX(TodosTreinosArea.numTreinos)
 ORDER BY 1, 2, 3

Upshot:

cod_area    descricao   cod_func    nome
1           Area A1     1           Jorge

Only the employee Jorge is returned because he is the only one who has completed all the necessary training: training 1 and training 2. Pedro is not listed because he only completed training 2 - he still has to complete training 1.

The result when the area selected is "Area B2" is as follows, as both Jorge and Pedro completed training 2:

cod_area    descricao   cod_func    nome
2           Area B2     1           Jorge
2           Area B2     3           Pedro 

The Sqlfiddle as an example.

Answer 2

I suppose there’s an employee table and a training table, right?

It would be something like:

DECLARE @QUANTIDADE_TREINAMENTOS int

SELECT @QUANTIDADE_TREINAMENTOS = count(*) FROM Treina

SELECT DISTINCT
    CodFunc,
    (SELECT count(*) from vw_treina_func TF2 where TF2.CodFunc = vw_treina_func.CodFunc AND TF2.CodTreina = vw_treina_func.CodTreina) as quantidade
FROM
    vw_treina_func TF
WHERE
    quantidade = @QUANTIDADE_TREINAMENTOS

This brings only the view records that have the same amount of training as the number of records in the training table.

Answer 3

Hello,

you must have a table with the trainings then it would be:

To take the total amount of materials

declare @qtdTreinamento bigint

SELECT qtdTreinamento  = (SELECT DISTINCT CodTreina FROM ***TREINAMENTO*** WHERE CodTreina IN (1,16,22,33,75,97110, 115, 158,161,164,173))

**Selecionar somente os que têm o total informado**

select * from **vw_treina_func**  where Matricula in (select matricula from vw_treina_func group by matricula having count(*) = @qtdTreinamento)