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
)
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.
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?
– jlHertel
I also didn’t understand, I tried to edit the question but I didn’t notice. I got lost.
– Reginaldo Rigo
"...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.
– George Wurthmann
@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 )
– Reginaldo Rigo
@Reginaldorigo makes perfect sense, but we’ll have to wait for him to edit to find out what he really needs. rs
– George Wurthmann
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.
– Luciano Silva
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.
– Luciano Silva
No one can help?
– Luciano Silva
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 ?
– Luciano Silva
I was able to solve the question by comparing arrays.
– Luciano Silva