SQL & PL/SQL

SQL> select * from t1;

                  ID C
-------------------- -
                   1 X
                   1 X
                   1 Y
                   2 X
                   2 Y
                   3 X
                   3 X
                   3 Y
                   3 Y
                   4 X
                   4 X
                   5 Y
                   5 Y

13 rows selected.

SQL> select id
  2  from   t1
  3  where  code = 'X'
  4  group  by id
  5  having count(*) = 2
  6  intersect
  7  select id
  8  from   t1
  9  where  code = 'Y'
 10  group  by id
 11  having count(*) = 1;

                  ID
--------------------
                   1

somewhat simpler:

select id
from t1
where code in ('X','Y')
group by id
having sum( decode(code,'X',1,0) ) = 2
and sum( decode(code,'Y',1,0) ) = 1

There is one more scenario added to this, the account number.

Only if the account number is different in code "X" , the record should be return, for example

ID CODE ACCNO
-----------------------------
1 X 1112
1 X 1113
1 Y 1112

2 X 1112
2 X 1112
2 Y 1112

3 X 1112
3 Y 1113

Only ID 1 should be returned because the accno within X is different and count is 2.

May be an in-line view with analytics?

select id
from (select id, 
             code,
             acctno,
             row_number() over (partition by id, 
                                             code, 
                                             acctno 
                                order by code) rn
      from t1
      )
where rn = 1
and code in ('X','Y')
group by id
having sum( decode(code,'X',1,0) ) = 2
and sum( decode(code,'Y',1,0) ) = 1

Cheers
Sarma.

Or an intersect

sql>select * from t;
ID CODE ACCNO  
1  X  1112  
1  X  1113  
1  Y  1112  
2  X  1112  
2  X  1112  
2  Y  1112  
3  Y  1113  
3  X  1112  

sql>
select id
from t
where code = 'X'
having count(distinct accno) = 2
group by id
intersect
select id
from t
where code = 'Y'
having count(accno) = 1
group by id;
ID  
1

jeneesh                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    

I've always considered set operators to be too heavy for performance. I don't generally think about SETs.

Cheers
Sarma.

Ummm....
Agreed...

Or something a little more convoluted...

SQL> ed
Wrote file afiedt.buf

  1  with t1 as (select 1 as id, 'X' as code, 1112 as acc_no from dual union all
  2  select 1, 'X', 1113 from dual union all
  3  select 1, 'Y', 1112 from dual union all
  4  select 2, 'X', 1112  from dual union all
  5  select 2, 'X', 1112 from dual union all
  6  select 2, 'Y', 1112 from dual union all
  7  select 3, 'X', 1112  from dual union all
  8  select 3, 'Y', 1113 from dual)
  9  -- END OF TEST DATA
 10  select id
 11  from (select id, code
 12              ,decode(lead(acc_no,1) over (partition by id, code order by id, code),
 13                      acc_no, null, decode(code, 'X', 1)) as same_acc
 14        from t1)
 15  where code in ('X','Y')
 16  group by id
 17  having sum( decode(code,'X',1,0) ) = 2
 18  and sum( decode(code,'Y',1,0) ) = 1
 19* and sum(same_acc) = 2
SQL> /

        ID
----------
         1

SQL>

PS. ignore the name "same_acc", it should really be "diff_acc" :)

Message was edited by:
blushadow