with 
product (id_product,name_product) as (
 select 1, 'Банан' from dual union all
 select 2, 'Груша' from dual union all
 select 3, 'Арбуз' from dual union all
 select 4, 'Вишня' from dual 
),
product_rev(id_product,success) as (
 select 1,'Y' from dual union all
 select 2,'N' from dual union all
 select 3,'Y' from dual union all
 select 3,'N' from dual union all
 select 4,'Y' from dual union all
 select 4,'Y' from dual 
)
select * 
from product p
where     exists (select * from product_rev pr where pr.id_product=p.id_product and pr.success='Y')
  and not exists (select * from product_rev pr where pr.id_product=p.id_product and pr.success='N')

select * 
from (
  select id_product 
    from product_rev 
  group by id_product 
  having count(*)>0
     and count(case when success='N' then 1 end) = 0
) q
join product p using (id_product)

SQL> ed
Wrote file afiedt.buf

  1  with product (id_product) as(
  2  select 1 from dual union all
  3  select 2 from dual union all
  4  select 3 from dual
  5  ),
  6  rev_product(id_rev,ref_product,result) as(
  7  select 10,1,'Проверка не успешна' from dual union all
  8  select 11,1,'Проверка успешна' from dual union all
  9  select 12,2,'Проверка успешна' from dual)
 10  select id_product from product p,rev_product r
 11  where ref_product=id_product
 12  group by id_product
 13* having count(nullif(result,'Проверка успешна'))=0 and count(nullif(result,'Проверка не успешна'))>0
SQL> /

ID_PRODUCT
----------
         2