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 34 35 36 37 38 39 40 41 42 43 44 45 46
| WITH t1 AS
(
SELECT 'c1' AS chef, 'p1' AS 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' , 'p3' FROM dual union ALL
SELECT 'c3' , 'p3' FROM dual
)
, t2 AS
(
SELECT 'r1' AS resto, 'p1' AS plat FROM dual union ALL
SELECT 'r1' , 'p2' FROM dual union ALL
SELECT 'r1' , 'p3' FROM dual union ALL
SELECT 'r2' , 'p2' FROM dual union ALL
SELECT 'r2' , 'p3' FROM dual union ALL
SELECT 'r3' , 'p4' FROM dual
)
, NB_PC AS
(
SELECT chef, count(distinct plat) as nb_pc
FROM t1
GROUP BY chef
)
, NB_PR AS
(
SELECT resto, count(distinct plat) as nb_rc
FROM t2
GROUP BY resto
)
SELECT t1.chef, t2.resto
FROM t1
INNER JOIN NB_PC nc
ON nc.chef = t1.chef
INNER JOIN t2
ON t2.plat = t1.plat
INNER JOIN NB_PR nr
ON nr.resto = t2.resto
WHERE nc.nb_pc = nr.nb_rc
GROUP BY t1.chef, t2.resto, nc.nb_pc
HAVING nc.nb_pc = count(*)
ORDER BY t1.chef asc, t2.resto asc;
CHEF RESTO
c1 r1
c2 r2 |
Partager