select a.name
from my_table b,my_table a
where a.name = b.name
and a.code between 'A1' AND 'A4'
AND B.CODE BETWEEN 'B1' AND 'B4'
GROUP BY a.name;

with t as (
           select  name,
                   sum(
                       distinct case
                                  when code between 'A1' AND 'A4' then 1
                                  when code between 'B1' AND 'B4' then 2
                                  else 4
                                end
                      ) over(partition by name) flag,
                   row_number() over(partition by name order by 1) rn
             from  my_table
          )
select  name
  from  t
  where flag = 3
    and rn = 1
/