Select from all records of an IN clause in Mysql

Asked

Viewed 582 times

3

I would like your help because I made a simple query that would need to return all records specified in clause IN. In this clause ( TF.Codtrains IN ) it comes from a select. The problem is that it is returning some employees who have some trainings. What I need is to present only the employees who have the training specified in the IN clause. The Detail is the employee cannot be from the area that will be coming from the filter, for example area code 1, because I want to show the replacements.

SELECT 
  FA.MatriculaSese, 
  TF.CodTreina,
  TF.Data,
  TF.Status, 
  FA.CentroCusto 
FROM treina_func TF 
INNER JOIN func_area FA 
  ON TF.MatriculaSese = FA.MatriculaSese 
WHERE FA.CentroCusto <> 1 
  AND TF.CodTreina IN (
    SELECT TA.CodTreina FROM treina_area TA WHERE TA.CodArea = 1 
  )

inserir a descrição da imagem aqui

inserir a descrição da imagem aqui

Trying to explain it better. The problem is that I would like to select only employees who have all the training of the IN clause. The training code for area 1 are IN( 1,16,32,64,122,136,168,169,170,171, 198, 199) I would like to not select all employees who have exactly all these training. In the code you are returning employees who have each other and not all. There is something that only returns employees who have all training in the IN clause?

Gentlemen redoing and my doubt I think now will be clearer

   SELECT * FROM vw_treina_func TF WHERE TF.CodTreina IN (1,16,22,33,75,97,110,115,158,161,164,173)

I would like this select to show only employees who have all these trainings and not another like this one.

  • 2

    Your question is a little confused. What exactly do you want to achieve? Could you give an example of the data contained in each table and the expected result?

  • 1

    I also didn’t understand, I tried to edit the question but I didn’t notice. I got lost.

  • 1

    "...the employee can not be from the area that will be coming from the filter, for example code of area 1..." Ta very confusing, but from what I understood above, just change to NOT IN.

  • 1

    @Georgewurthmann I think that the substitudes of the employees of area 1 are not the effective of the other areas, therefore the NOT IN nonemployee. I think it would have to be something like this: IN( SELECT TA.CodTreina FROM treina_area TA WHERE TA.CodArea = 1 AND SUBSTITUTO = 1 )

  • @Reginaldorigo makes perfect sense, but we’ll have to wait for him to edit to find out what he really needs. rs

  • The training_area table contains all the training an employee has to have to work in the company area. The training_func table records all training that the employee has. What I’m needing now is to know the employees who have all training of area 1 by this: TF.Codtrains IN only that the employee can not be effective of area 1 because this query has to show the employees of others that can replace one of the effective employees of area 1. I will try to insert the image that is generated from the employee interface x training X area.

  • I just added the list of employees in area 1 with the proper training for area 1. this is the actual employees in the area. My difficulty and select to show employees who also have all these 12 training but who are not from this area 1.

  • No one can help?

  • Someone? Just need the result to bring me all the employees you have within the IN clause . Infezlimente the IN brings one another and not all ?

  • I was able to solve the question by comparing arrays.

Show 5 more comments
No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.