I need to write a oracle sql query for the below.

The table “employee” contains columns: ENO, Sequence and Status of 3 employees. I need to retrieve only those employees who has Status as both "New" and "Old". So, I do not need employee no: C456. Please see below expected output.

Final expected output is below:

CodePudding user response：

You can use these queries:

1) select *
     from employee a
    where exists (select 1 from employee b where b.ENO = a.ENO and b.status = 'New')
      and exists (select 1 from employee b where b.ENO = a.ENO and b.status = 'Old')
    order by 1,2;

ENO    SEQUENCE STATUS
---- ---------- -------
A101          1 New
A101          2 Old
A101          3 Old
A101          4 Old
B111          3 Old
B111          5 New
B111         25 New

2) select *
     from employee a
    where (select count(distinct b.status) 
             from employee b where b.ENO = a.ENO and b.status in ('New','Old')) = 2
    order by 1,2;
              
ENO    SEQUENCE STATUS
---- ---------- -------
A101          1 New
A101          2 Old
A101          3 Old
A101          4 Old
B111          3 Old
B111          5 New
B111         25 New
          

Thank you.

CodePudding user response：

Here's a simple solution using count() over() without inner joins.

select  ENO
       ,Sequence
       ,Status
from   (
        select  t.*
                  ,count(distinct status) over(partition by eno) as cnt
        from    t
       ) t
where   cnt = 2

Fiddle