I have a query like
select * from emp where emp.id in(1,2,3) and emp.sal>10000
I need to add a logic to the check emp.sal>10000 only if design.type='Labor', I tried like the below but not working
select * from emp,design where emp.id in(1,2,3) and case when design.type='Labor' then emp.sal>10000
Can we implement this ?
Simple:
select * from emp where emp.id in(1,2,3) and (design.type<>'Labor' OR emp.sal>10000)
If design.type='Labor' then emp.sal must be greater than 10000 for the condition to be true.
You want to query rows that have a salary higher than 10000 only when the type is labor for completion's sake, we will also state that for different types we don't care about the salary. If we put this formally, we'll get:
(type = 'Labor' and salary > 10000) OR (type != 'Labor')
This boolean expression can of course be simplified - we're looking for rows that either have a type different than Labor or a salary higher than 10000:
(salary > 10000) OR (type != 'Labor')
So if we put it all together:
SELECT *
FROM emp
WHERE (emp.id IN (1,2,3)) AND (type != 'Labor' OR sal > 10000)
Try this.
select * from
(
select * from emp where id in (1,2,3) and design.type='Labor'
)a
where a.sal>10000;
This should work for you...
SELECT * FROM emp
WHERE emp.id IN (1,2,3)
AND ((design.type = 'Labor' AND emp.sal>10000)
OR (design.type <> 'Labor'))
The simplification of the OR condition would be..
SELECT * FROM emp
WHERE emp.id IN (1,2,3)
AND (design.type <> 'Labor' OR emp.sal > 10000)
