CODEWITHSUNDEEP
sqloracle-database
user1017936
user1017936

Reputation: 153

Oracle sql query with all mandatory values in where condition

I want to write one select query in ORACLE which will return records only if all values given in where condition exists. E.g.

select * from emp where empid in (7521,7566,7698)

Now I want to write this query so that it should return value ONLY when all 3 empid exists. It will be like AND condition i.e. empid = 7521 and empid = 7566 and empid = 7698. If any one value does not exist then this query should not fetch any row.

Upvotes: 2

Views: 2644

Answers (2)

user330315
user330315

Reputation:

An extension to Lukas' version where you need to write the IDs only once: 

with emp_ids as (
   select 7521 as eid from dual
   union all 
   select 7566 from dual
   union all
   select 7698 from dual
)
select * 
from (
  select emp.*, 
         count(*) over() as cnt
  from emp_new emp
  where empid in (select eid from emp_ids)
)
where cnt = (select count(*) from emp_ids);

In these cases I always miss the standard row constructor that is available in other DBMS, where I can simply write VALUES (7521), (7566), (7698) to generate a virtual table with the desired values without the need to use DUAL...

Upvotes: 3

Lukas Eder
Lukas Eder

Reputation: 220787

Run the same query again, as a nested select, and count its records

select * from emp 
where empid in (7521, 7566, 7698)
and 3 = (select count(*) from emp where empid in (7521, 7566, 7698))

Alternatively, use an analytic function on the original result and check on that:

select * from (
  select emp.*, count(*) over() as cnt
  from emp where empid in (7521, 7566, 7698)
)
where cnt = 3

Upvotes: 4

Related Questions