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 47 48 49 50 51 52
| With FIGURE (ID, NAME) AS
(
select 1, 'FIGURE1' union all
select 2, 'FIGURE2' union all
select 3, 'FIGURE3' union all
select 4, 'FIGURE4' union all
select 5, 'FIGURE5'
)
, CATEGORY_TYPE (ID, TYPE) AS
(
select 1, 'COULEUR' union all
select 2, 'FORME'
)
, CATEGORY (ID, VALUE, CATEGORY_TYPE_ID) AS
(
select 1, 'ROND' , 2 union all
select 2, 'CARRE', 2 union all
select 3, 'VERT' , 1 union all
select 4, 'ROUGE', 1 union all
select 5, 'JAUNE', 1
)
, FORME_COLLECTION (ID_CATEGORY, ID_FIGURE) AS
(
select 1, 1 union all
select 3, 1 union all
select 4, 2 union all
select 2, 3 union all
select 5, 3 union all
select 3, 4 union all
select 1, 5 union all
select 4, 5
)
SELECT
FG.NAME AS FIGNAME,
CG.VALUE AS FORMNAME
FROM
FIGURE AS FG
LEFT OUTER JOIN FORME_COLLECTION AS FC
INNER JOIN CATEGORY AS CG
ON CG.ID = FC.ID_CATEGORY
INNER JOIN CATEGORY_TYPE AS CT
ON CT.ID = CG.CATEGORY_TYPE_ID
AND CT.TYPE = 'FORME'
ON FC.ID_FIGURE = FG.ID;
FIGNAME FORMNAME
------- --------
FIGURE1 ROND
FIGURE2 NULL
FIGURE3 CARRE
FIGURE4 NULL
FIGURE5 ROND |
Partager