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 |
