The gist is, I want to return a list of employee_id numbers for the IN clause.
I am given a list of employee_ids like (2,3,4,5) and a company_id like 5. Some of these belong to company=5, some do not. If any of these belong to company=5, then I want to ONLY use those id numbers in the IN clause. If NONE of the given employee_id numbers belongs to company=5, then I want to return ALL employee_id numbers for company=5.
I have the logic here, but unfortunately you can't return multiple values inside a WHEN clause from a sub select so this statement returns an error.
I am trying to modify an existing WHERE clause so I need the changes to be in place in this statement and without the use of cursors or the like. I know there must be a way to convert these to a series of AND/OR, but I cannot figure it out.
Thank you for any help!
SELECT employee_id FROM employees
WHERE company_id=5
AND employee_id IN (
CASE
WHEN NOT EXISTS(SELECT employee_id FROM employees WHERE company_id=5 AND employee_id IN (2,3,4,5)) THEN
SELECT employee_id FROM employees WHERE company_id=5
ELSE
SELECT employee_id FROM employees WHERE company_id=5 AND employee_id IN (2,3,4,5)
END
)
Will this work?
SELECT employee_id FROM employees
WHERE company_id=5
and employee_id IN (2,3,4,5)
and exists (select * from employees where company_id = 5 and employee_id IN (2,3,4,5))
union all
SELECT employee_id FROM employees
WHERE company_id=5
and Not exists (select * from employees where company_id = 5 and employee_id IN (2,3,4,5))
Only one of the exists/not exists can fire. So this will do one select or the other, but without the use of If or Case statements. The first exists is probably unnecessary... so
SELECT employee_id FROM employees
WHERE company_id=5
and employee_id IN (2,3,4,5)
union all
SELECT employee_id FROM employees
WHERE company_id=5
and Not exists (select * from employees where company_id = 5 and employee_id IN (2,3,4,5))