List only if the other table does not have a dismissal

Question

Good afternoon. I need to make a list of employees per year. For example. Active employees 2018, However I’m having problems, because in 2019 some were fired there not list the same, and use the table Employees and Examesfuncionarios.

Edited: Next, I used the check to know if I had exams resigned, so far so good, I did not list this person, but that same person has in the same year the exam return to work, or even another exam and I do not want and I can not display it. How do I make the condition that if there is exam resigned for such person it jumps the person even if it has other exams.

Follow the Codes and tables.

Employee Table ->

 CREATE TABLE `funcionario` (
  `CodFuncionario` int(11) NOT NULL AUTO_INCREMENT,
  `funcionario_CodEmpresa` int(11) NOT NULL,
  `funcionario_CodSetor` int(11) DEFAULT NULL,
  `funcionario_CodCBO` varchar(6) DEFAULT NULL,
  `funcionario_Nome` varchar(50) DEFAULT NULL,
  `funcionario_DataNac` date DEFAULT NULL,
  `funcionario_DataAdm` date DEFAULT NULL,
  `funcionario_Sexo` varchar(1) DEFAULT NULL,
  `funcionario_CTPS` varchar(20) DEFAULT NULL,
  `funcionario_RG` varchar(10) DEFAULT NULL,
  `funcionario_NIT` varchar(12) DEFAULT NULL,
  `funcionario_Ativo` varchar(1) DEFAULT NULL,
  `funcionario_Cpf` varchar(11) DEFAULT NULL,
  `funcionario_DataDemissao` date DEFAULT NULL,
  `funcionario_TrabalhoAltura` varchar(1) DEFAULT NULL,
  `funcionario_TrabalhoConfinado` varchar(1) DEFAULT NULL,
  `funcionario_HoraEntrada` varchar(100) DEFAULT NULL,
  `funcionario_HoraSaida` varchar(100) DEFAULT NULL,
  `funcionario_InicioRepouso` varchar(100) DEFAULT NULL,
  `funcionario_FimRepouso` varchar(100) DEFAULT NULL,
  `funcionario_Epi` longtext DEFAULT NULL,
  `funcionario_RiscoFisico` longtext DEFAULT NULL,
  `funcionario_RiscoQuimico` longtext DEFAULT NULL,
  `funcionario_RiscoBiologico` longtext DEFAULT NULL,
  `funcionario_RiscoErgonomico` longtext DEFAULT NULL,
  `funcionario_HoraEntrada_S` varchar(100) DEFAULT NULL,
  `funcionario_HoraSaida_S` varchar(100) DEFAULT NULL,
  `funcionario_InicioRepouso_S` varchar(100) DEFAULT NULL,
  `funcionario_FimRepouso_S` varchar(100) DEFAULT NULL,
  `funcionario_ExameOcupacional` longtext DEFAULT NULL,
  PRIMARY KEY (`CodFuncionario`) USING BTREE
)

Tebela Examesfuncionarios ->

CREATE TABLE `examefuncionario` (
  `idExameFunc` int(11) NOT NULL AUTO_INCREMENT,
  `exameFunc_CodFuncionario` int(11) NOT NULL,
  `exameFunc_NrSeq` int(11) NOT NULL,
  `exameFunc_CodExame` int(11) DEFAULT NULL,
  `exameFunc_Data` date DEFAULT NULL,
  `exameFunc_Tipo` varchar(1) DEFAULT NULL,
  `exameFunc_Natureza` varchar(1) DEFAULT NULL,
  `exameFunc_Apto` varchar(1) DEFAULT NULL,
  `exameFunc_DataProximo` date DEFAULT NULL,
  `exameFunc_Resultado` varchar(1) DEFAULT NULL,
  `exameFunc_FlgRefSeq` varchar(1) DEFAULT NULL,
  `exameFunc_DataUltimo` date DEFAULT NULL,
  PRIMARY KEY (`idExameFunc`) USING BTREE
)

Mysql codes 1st form ->

select funcionario.CodFuncionario,funcionario.funcionario_Nome
from funcionario
join examefuncionario on examefuncionario.exameFunc_CodFuncionario = funcionario.CodFuncionario and date_format(examefuncionario.exameFunc_Data, "%Y") = '2018'
where funcionario_CodEmpresa = 179 
and date_format(funcionario_DataAdm, "%Y") <= '2018'
group by funcionario.CodFuncionario
order by funcionario.funcionario_Nome ASC

Mysql codes 2nd form ->

select funcionario.CodFuncionario,funcionario.funcionario_Nome
from funcionario
join examefuncionario on examefuncionario.exameFunc_CodFuncionario = funcionario.CodFuncionario and date_format(examefuncionario.exameFunc_Data, "%Y") = '2018'
where funcionario_CodEmpresa = 179 
and date_format(funcionario_DataAdm, "%Y") <= '2018'
and funcionario_Ativo = 'S' 
group by funcionario.CodFuncionario
order by funcionario.funcionario_Nome ASC

Edited code:

select *
from funcionario

join examefuncionario 
    on examefuncionario.exameFunc_CodFuncionario = funcionario.CodFuncionario 
    and date_format(examefuncionario.exameFunc_Data, "%Y") = '2018'
    and examefuncionario.exameFunc_Natureza != 'D'

where funcionario_CodEmpresa = 179 
and date_format(funcionario_DataAdm, "%Y") <= '2018'

group by funcionario.CodFuncionario
order by funcionario.funcionario_Nome ASC

I need you to list the employees who didn’t have their exams in 2018. In 2018 the employee was active but in 2019 is inactive, so when I use the Where of the same some who in 2018 were active not list.

Answer 1

Use the NOT EXISTS as follows:

SELECT f.CodFuncionario,
       f.funcionario_Nome
  FROM funcionario f
 WHERE f.funcionario_CodEmpresa = 179
   AND EXISTS(SELECT 1
                FROM examefuncionario ef
               WHERE ef.exameFunc_CodFuncionario = f.CodFuncionario
                 AND DATE_FORMAT(ef.exameFunc_Data, "%Y") = '2018'
                 AND ef.exameFunc_Natureza <> 'D')
   AND NOT EXISTS(SELECT 1
                    FROM examefuncionario ef
                   WHERE ef.exameFunc_CodFuncionario = f.CodFuncionario
                     AND DATE_FORMAT(ef.exameFunc_Data, "%Y") = '2018'
                     AND ef.exameFunc_Natureza = 'D')
 GROUP BY f.CodFuncionario
 ORDER BY f.funcionario_Nome ASC

Answer 2

I believe you have to also put conditions related to the type or nature of examination. Just looking at the samples of the tables you passed, I get a little bit in doubt of what it would be:

exameFunc_Tipo     varchar(1) DEFAULT NULL,
exameFunc_Natureza varchar(1) DEFAULT NULL,

It might solve your problem if you put a NOT IN condition to catch employees who have not had type/nature examinations resigned in the year 2018, you understand?

There is another question... when doing the Join with the Table Examsfunctions, you already take into consideration that every employee active in 2018 did some kind of exam. You have to assess if this condition is true. Every year every employee of the company goes through some kind of examination every year? Because if you don’t pass, I imagine there will be no record in the table of employee exams... if there is no record, when you do Join, those employees who have not taken any exam in 2018 will automatically be out of the query result.