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
|
CREATE TABLE table1 (
ID INTEGER NOT NULL,
LABEL_ID INTEGER NOT NULL,
...
);
ALTER TABLE table1 ADD CONSTRAINT pk_table1 PRIMARY KEY (ID);
CREATE TABLE table2 (
LABEL_ID INTEGER NOT NULL,
LANGUAGE_ID INTEGER NOT NULL,
LABEL VARCHAR(100)
);
ALTER TABLE table2 ADD CONSTRAINT pk_table2 PRIMARY KEY (LABEL_ID, LANGUAGE_ID);
CREATE VIEW table1_view (
ID, ..., LABEL_ID,
LANGUAGE_ID, LABEL, SIMPLE_LABEL)
AS
SELECT
T1.ID, ..., T1.LABEL_ID,
T2.LANGUAGE_ID,
CASE WHEN T3.id IS NOT NULL THEN T2.label||' ('||T3.label||')' ELSE T2.label END,
T2.label
FROM table1 T1
INNER JOIN table2 T2 ON T1.LABEL_ID = T2.LABEL_ID
LEFT JOIN table3 T3 ON T3.id = T3.table1id
^ |
Partager