1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33
|
with T1 as ( select 'c1'chef, 'p1' plat from dual union all
select 'c1','p2' from dual union all
select 'c1','p3' from dual union all
select 'c2','p2' from dual union all
select 'c2','p4' from dual union all
select 'c3','p5' from dual union all
select 'c4','p6' from dual )
, T2 as ( select 'r1' resto,'p1' plat from dual union all
select 'r1' ,'p2'from dual union all
select 'r2','p2' from dual union all
select 'r2','p3' from dual union all
select 'r2','p4' from dual union all
select 'r3','p2' from dual union all
select 'r3','p5' from dual union all
select 'r3','p6' from dual )
SELECT resto,chef
FROM (SELECT resto, chef, COUNT (1) OVER (PARTITION BY t2.plat)
nb_plat_resto
FROM t1, t2
WHERE t1.plat = t2.plat)
WHERE nb_plat_resto = 1
RESTO CHEF
--------------- ---------------
r1 c1
r2 c1
r2 c2
r3 c3
r3 c4
5 rows selected. |
Partager