select a.field, a.description, a.subject
   into :d_block.field, :d_block.description, :d_blcok.subject
  from table1 a, table2 b, table3 c
  where a.rp_id = b.rp_id
  and c.creiteria ='email';

where rp_id in (select t3.rp_id from table3 t3
                      where t3.creiteria = 'email')

select a.field, a. descp, a. subject, c. email, d.line
into :d_blcok1.field,
     :d_blcok1.descp,
     :d_blcok1.subject,
     :d_block2.email
     :d_block3.line
from table1 a, table2, b, table3 c, table4 d
where a.rp_id in ( select b.rp_id from table2 b where b.criteria = 'email')
and a.rp_id = c.rp_id
and a.rp_id = d.rp_id;

Thanking you.

select t1.*
from table1 t1
where t1.rp_id in (select t3.rp_id from table3 t3
                   where t3.creiteria = 'email'
                  );