Hello.
I'm trying to combine two tables with Union. I managed to do that, but I need to check if one value exists in table 1 (V_AuditoriaEventoServidor) before selecting the corresponding null value in table 2 (SHC_CapacitacaoNaoInstitucional). If value does not exists in table 1, then I want to select the null value in table 2. If value does exists in table 1, then I should not select null value in table 2.
I'm combining the two tables using UNION.
Where should I insert my IF? Should I declare a variable and set it to TRUE if value exists? How can I go on about doing this?
Any help is appreciated.
Thank you in advance.
Here's my script:
//If Empregado.Curso exists in this table then null value from second table should not count
SQL SELECT NomeEmpregado as Servidor,
evento."DataIniRealizacao" as Início,
evento."DataFimRealizacao" as Fim,
evento."CargaHoraria" as Horas,
evento."descEvento" as Curso
FROM SARH.dbo.Empregado
LEFT JOIN SDTA.dbo."V_AuditoriaEventoServidor" as evento
ON evento.numCpf = Empregado.NumCpf
LEFT JOIN SARH.dbo.DadosFuncionais as dados
ON dados.CodEmpregado = Empregado.CodEmpregado
JOIN SARH.dbo.Orgao as orgao
ON orgao.CodOrgao = dados.CodUltimaLotacao
AND orgao.SiglaOrgao LIKE 'GR08%'
WHERE dados.DataSaidaEmpregado is null
UNION
SELECT NomeEmpregado as Servidor2,
capacitacao."DataInicio" as Início2,
capacitacao."DataFim" as Fim2,
capacitacao."CargaHoraria" as Horas2,
capacitacao."descTitulo" as Curso2
FROM SARH.dbo.Empregado
LEFT JOIN SDTA.dbo."SHC_CapacitacaoNaoInstitucional" as capacitacao
ON capacitacao.CodEmpregado = Empregado.CodEmpregado
LEFT JOIN SDTA.dbo."V_AuditoriaEventoServidor" as evento2
ON evento2.numCpf = Empregado.NumCpf
LEFT JOIN SARH.dbo.DadosFuncionais as dados
ON dados.CodEmpregado = Empregado.CodEmpregado
JOIN SARH.dbo.Orgao as orgao
ON orgao.CodOrgao = dados.CodUltimaLotacao
AND orgao.SiglaOrgao LIKE 'GR08%'
WHERE dados.DataSaidaEmpregado is null;
