I have two table

Employee

EmployeeResidence

I need a Sql query that returns 1 or 0 based on the Bind value Employee id and I need the following conditions to be validated

1. job should not be null 2)Nationality should not be null and he should belong to MEX

So how can i do that ? I am building something like this

select 1 from Employee emp ,EmployeeResidence er 
where 
employeeid=:empid and emp.job is not null and 

I am struggling to include EmployeeSpclField column value to check if it is not null and belongs to MEX

CodePudding user response：

I might avoid a join and instead use exists logic here:

SELECT e.*
FROM Employee e
WHERE Job IS NOT NULL AND
      EXISTS (SELECT 1 FROM EmployeeResidence er
              WHERE er.EmployeeId = e.EmployeeId AND
                    er.EmployeeSpclField = 'Nationality' AND
                    er.EmployeeSpclValue = 'MEX');

CodePudding user response：

Select a 0/1 flag

select case when exists (
    select 1 
    from Employee emp 
    join EmployeeResidence er 
      on emp.employeeid = er.employeeid and er.EmployeeSpclField = 'Nationality' and er.EmployeeSpclValue = 'MEX'
    where emp.employeeid=:empid and emp.job is not null ) then 1
   else 0 end flag 
from dual;

CodePudding user response：

Try this:

Select
e.EmployeeID,
case when er.EmployeeID is null then 0 else 1 end as return_val
from
Employee e
left outer join
(select EmployeeId,
 max(case when EmployeeSpclField='Married' then EmployeeSpclValue else NULL end) as married,
 max(case when EmployeeSpclField='Nationality' then EmployeeSpclValue else NULL end) as Nationality,
 max(case when EmployeeSpclField='Job' then EmployeeSpclValue else NULL end) as Job
from 
 EmployeeResidence
group by
EmployeeId) er
on e.EmployeeId=er.EmployeeId
and e.Job is not null 
and (er.Nationality is not null and er.Nationality ='MEX')