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
| CREATE TABLE T_MOTCLEF_MCF
(MCF_ID INT NOT NULL PRIMARY KEY,
MCF_MOT VARCHAR(32) NOT NULL)
INSERT INTO T_MOTCLEF_MCF VALUES (1, 'beurre')
INSERT INTO T_MOTCLEF_MCF VALUES (2, 'tartine')
INSERT INTO T_MOTCLEF_MCF VALUES (3, 'confiture')
INSERT INTO T_MOTCLEF_MCF VALUES (4, 'entrée')
INSERT INTO T_MOTCLEF_MCF VALUES (5, 'viande')
CREATE TABLE T_CONCEPT_CCP
(CCP_ID INT NOT NULL PRIMARY KEY,
CCP_LIBELLE VARCHAR(32) NOT NULL)
INSERT INTO T_CONCEPT_CCP VALUES (1, 'déjeuner')
INSERT INTO T_CONCEPT_CCP VALUES (2, 'Petit-déjeuner')
INSERT INTO T_CONCEPT_CCP VALUES (3, 'dîner')
CREATE TABLE T_MOT_CONCEPT_MCP
(MCP_ID INT NOT NULL PRIMARY KEY,
MCF_ID INT NOT NULL FOREIGN KEY REFERENCES T_MOTCLEF_MCF (MCF_ID),
CCP_ID INT NOT NULL FOREIGN KEY REFERENCES T_CONCEPT_CCP (CCP_ID))
INSERT INTO T_MOT_CONCEPT_MCP VALUES (1, 3, 1)
INSERT INTO T_MOT_CONCEPT_MCP VALUES (2, 4, 2)
INSERT INTO T_MOT_CONCEPT_MCP VALUES (3, 4, 3)
INSERT INTO T_MOT_CONCEPT_MCP VALUES (4, 5, 3)
INSERT INTO T_MOT_CONCEPT_MCP VALUES (5, 5, 2)
SELECT CCP_LIBELLE
FROM T_MOT_CONCEPT_MCP AS MCP
INNER JOIN T_MOTCLEF_MCF AS MCF
ON MCP.MCF_ID = MCF.MCF_ID
INNER JOIN T_CONCEPT_CCP AS CCP
ON MCP.CCP_ID = CCP.CCP_ID
WHERE MCF_MOT IN ('viande', 'entrée')
GROUP BY CCP_LIBELLE
HAVING COUNT(*) = 2 |
Partager