2

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.

Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
user1017936
  • 143
  • 1
  • 5
  • 14
  • Check out http://stackoverflow.com/q/4672545/55922 for how to turn a list of values into something you can use in an IN statement. From there, to make sure you have all of them I would recommend using the analytic function to count the values suggested by a_horse_with_no_name and make sure that matches the number of items in your list, which can be found using length(id_list) - length(replace(id_list,',')) + 1 – Craig Oct 28 '11 at 14:17

2 Answers2

4

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
Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
  • but here my in condition is dynamic, it is not fixed to 3 only – user1017936 Oct 28 '11 at 08:17
  • @user1017936: I imagine, you can render it dynamically, then? Where do you store the relevant ID's? – Lukas Eder Oct 28 '11 at 08:23
  • Actually it is used to for some search condition. Yes I can take count but again there is one problem user will enter values in string with comma separated values so I cant use IN query. – user1017936 Oct 28 '11 at 08:30
  • 1
    @user1017936: I'm not sure if I understand... But I guess that's a new unrelated problem, which deserves a new question. As far as the query goes, I gave you two valid options. – Lukas Eder Oct 28 '11 at 08:55
3

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

  • Now there are some complexities here i.e. the eid which we are retrieving from emp_ids also have some parents, So my count will increase there. – user1017936 Oct 28 '11 at 14:11
  • That's not what you described in your question or your comments to Lukas' answer (and I'm not sure what you mean with "*have some parent*") –  Oct 28 '11 at 14:16
  • Nice solution. I haven't thought of CTE's for constructing ad-hoc in-memory tables. Indeed, it's a pity, that there isn't even any default `TABLE` or `VARRAY` type available in Oracle... – Lukas Eder Oct 29 '11 at 10:57
  • @user1017936: if that solution works perfectly, perhaps you should accept it. – Adam Musch Mar 06 '12 at 08:21