with 
klients (kid,fam,zod) as (
select 1,'Kira','Дева' from dual union all
select 2,'Stax','Козерог' from dual union all
select 3,'Mitt','Близнецы' from dual union all
select 4,'Anna','Рак' from dual union all
select 5,'Scott','Рыба' from dual union all
select 6,'Tiger','Лев' from dual)
,sovmestimost (zod1,zod2) as(
select 'Дева','Близнецы' from dual union all
select 'Дева','Рыба' from dual union all
select 'Лев','Рыба' from dual union all
select 'Рак','Рыба' from dual union all
select 'Рак','Лев' from dual)
,trebovania (kid,sovmest) as (
select 1,'+'  from dual union all
select 2,'+'  from dual union all
select 3,null from dual union all
select 4,null from dual union all
select 5,'+'  from dual union all
select 6,null from dual)
select 
  k1.kid kid1
 ,k1.fam fam1
 ,k2.kid kid2
 ,k2.fam fam2
from 
  klients k1
 ,trebovania t
 ,klients k2
 ,sovmestimost s
where 
    k1.kid=t.kid 
and t.sovmest is not null
and k1.kid<>k2.kid
and (s.zod1=k1.zod and s.zod2=k2.zod or s.zod1=k2.zod and s.zod2=k1.zod)
--and rownum=1 --Нужно вывести ID двух клиентов (если надо раскоментировать)
order by 1
/

SQL> /

      KID1 FAM1        KID2 FAM2
---------- ----- ---------- -----
         1 Kira           5 Scott
         1 Kira           3 Mitt
         5 Scott          4 Anna
         5 Scott          1 Kira
         5 Scott          6 Tiger