T-SQL - Return list of values to IN clause depending on another list of values

Question

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
  )

score 1 · Accepted Answer · answered Nov 28 '12 at 23:03

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))

T-SQL - Return list of values to IN clause depending on another list of values

1 Answers1