Select Where IN all records

4

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
    
asked by anonymous 08.05.2017 / 20:28

3 answers

3

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 statement: HAVING COUNT (DISTINCT TF.CodTreina) = 12 This will limit the results to employees who have 12 different CodTreina.

Edit after @LucianoSilva's comment

The goal is to identify which employees have performed 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 loaded 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 performed 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

Result:

cod_area    descricao   cod_func    nome
1           Area A1     1           Jorge

Only the official 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 is not completing training 1.

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

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

Here's also SQLFiddle as an example.

    
08.05.2017 / 20:54
2

I suppose there is a staff and training table, right?

It would be anything 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 view records that have the same amount of training as the number of records in the training table.

    
08.05.2017 / 20:32
0

Hello,

You should have a table with the trainings then it would be:

To get the total amount of material

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)
    
08.05.2017 / 20:58