SQL & PL/SQL

Problem 1 Can be solved like:

SQL> select * from t;

        ID CODE
---------- ----------
         1 aaa
         1 bbb
         1 ccc
         2 aaa
         2 bbb
         2 ddd
         3 bbb
         3 eee

8 rows selected.

SQL> select id
  2  from t
  3  group by id
  4  having sum(case when code like 'a%' then 1 else 0 end) > 0
  5  and sum(decode(code,'bbb',1,0)) > 0
  6  and sum(decode(code,'ccc',1,0)) > 0;

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

<br>
<br>

Similarly Problem 2 can also be solved...                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        

create table T(ID,Code) as
select 1,'aaa' from dual union
select 1,'bbb' from dual union
select 1,'ccc' from dual union
select 2,'aaa' from dual union
select 2,'bbb' from dual union
select 2,'ddd' from dual union
select 3,'bbb' from dual union
select 3,'eee' from dual;

1. Set should have at least one record with code like 'a%'
and should have at least one record for codes 'bbb', 'ccc'
(in this case group 1 would be selected)

select ID
  from T
group by ID
having max(case when code like 'a%' then 1 else 0 end)
     * max(case when code = 'bbb' then 1 else 0 end)
     * max(case when code = 'ccc' then 1 else 0 end) = 1;

ID
--
 1

2. Set should have at least one record with code like 'a%'
and should have at least one record for either 'bbb' or 'ccc'
(in this case group 1and 2 would be selected)

select ID
  from T
group by ID
having  max(case when code like 'a%' then 1 else 0 end)
           * (max(case when code = 'bbb' then 1 else 0 end)
            + max(case when code = 'ccc' then 1 else 0 end)) >= 1;

ID
--
 1
 2

or

select ID
  from T
group by ID
having max(case when code like 'a%'        then 1 else 0 end)
     * max(case when code in ('bbb','ccc') then 1 else 0 end) = 1;

similar threads
551061
550921
526715
551338

Hi, Pravin,

If efficiency is important, then you should do one test directly (preferably the one that rules out the most groups), and use corellated EXISTS subqueries for the other tests.
For example, to find ids with 'a%' and both 'bbb' and 'ccc':

SELECT DISTINCT	id
FROM	t	m	-- m for main
WHERE	code	= 'ccc'
AND	EXISTS
	(	-- Begin subquery to find 'a%'
	SELECT	NULL
	FROM	t
	WHERE	id	= m.id
	AND	code	LIKE 'a%'
	)	-- End subquery to find 'a%'
AND	EXISTS
	(	-- Begin subquery to find 'bbb'
	SELECT	NULL
	FROM	t
	WHERE	id	= m.id
	AND	code	= 'bbb'
	)	-- End subquery to find 'bbb'
;

select id,code
from
(
select id,
       code,
       sum(case when code like 'a%' then 1 else 0 end) over (partition by id) num_a,
       sum(case code when 'bbb'     then 1 else 0 end) over (partition by id) num_bbb,
       sum(case code when 'ccc'     then 1 else 0 end) over (partition by id) num_ccc
from   t
)
where num_a >= 1 and
      num_bbb >= 1 and
      num_ccc >= 1
/
      
select id,code
from
(
select id,
       code,
       sum(case when code like 'a%' then 1 else 0 end) over (partition by id) num_a,
       sum(case code when 'bbb'     then 1 else 0 end) over (partition by id) num_bbb,
       sum(case code when 'ccc'     then 1 else 0 end) over (partition by id) num_ccc
from   t
)
where num_a >= 1 and
      (num_bbb >= 1 or
       num_ccc >= 1)
/