qmZpWDgv

1DROP TABLE IF EXISTS CAT CASCADE;
2
3CREATE TABLE CAT (
4  ID   BIGINT,
5  NAME CHARACTER VARYING(255) NOT NULL UNIQUE
6);
7
8ALTER TABLE CAT
9  ADD CONSTRAINT CAT_PK PRIMARY KEY (ID);
10
11DROP TABLE IF EXISTS PROD CASCADE;
12
13CREATE TABLE PROD (
14  ID   BIGINT,
15  NAME CHARACTER VARYING(255) NOT NULL UNIQUE
16);
17
18ALTER TABLE PROD
19  ADD CONSTRAINT PROD_PK PRIMARY KEY (ID);
20
21DROP TABLE IF EXISTS PROD_CAT;
22
23CREATE TABLE PROD_CAT (
24  PROD_ID BIGINT NOT NULL,
25  CAT_ID  BIGINT NOT NULL
26);
27
28ALTER TABLE PROD_CAT
29  ADD CONSTRAINT PROD_FK FOREIGN KEY (PROD_ID) REFERENCES PROD (ID);
30ALTER TABLE PROD_CAT
31  ADD CONSTRAINT CAT_FK FOREIGN KEY (CAT_ID) REFERENCES CAT (ID);
32ALTER TABLE PROD_CAT
33  ADD CONSTRAINT PROD_CAT_UNQ UNIQUE (PROD_ID, CAT_ID);
34
35DROP TABLE IF EXISTS PROD_VARIANT;
36
37CREATE TABLE PROD_VARIANT (
38  ID      BIGINT,
39  NAME    CHARACTER VARYING(255) NOT NULL UNIQUE,
40  PROD_ID BIGINT                 NOT NULL,
41  ACTIVE  BOOLEAN
42);
43
44ALTER TABLE PROD_VARIANT
45  ADD CONSTRAINT PROD_VARIANT_PK PRIMARY KEY (ID);
46ALTER TABLE PROD_VARIANT
47  ADD CONSTRAINT PROD_FK FOREIGN KEY (PROD_ID) REFERENCES PROD (ID);
48
49INSERT INTO CAT (ID, NAME) VALUES (1, 'CAT 1');
50INSERT INTO CAT (ID, NAME) VALUES (2, 'CAT 2');
51INSERT INTO CAT (ID, NAME) VALUES (3, 'CAT 3');
52
53INSERT INTO PROD (ID, NAME) VALUES (1, 'PROD 1');
54INSERT INTO PROD (ID, NAME) VALUES (2, 'PROD 2');
55INSERT INTO PROD (ID, NAME) VALUES (3, 'PROD 3');
56
57INSERT INTO PROD_CAT (PROD_ID, CAT_ID) VALUES (1, 1);
58INSERT INTO PROD_CAT (PROD_ID, CAT_ID) VALUES (2, 2);
59INSERT INTO PROD_CAT (PROD_ID, CAT_ID) VALUES (3, 3);
60
61INSERT INTO PROD_VARIANT (ID, NAME, PROD_ID, ACTIVE) VALUES (1, 'VAR 1', 1, TRUE);
62INSERT INTO PROD_VARIANT (ID, NAME, PROD_ID, ACTIVE) VALUES (2, 'VAR 2', 1, TRUE);
63INSERT INTO PROD_VARIANT (ID, NAME, PROD_ID, ACTIVE) VALUES (3, 'VAR 3', 2, TRUE);
64INSERT INTO PROD_VARIANT (ID, NAME, PROD_ID, ACTIVE) VALUES (4, 'VAR 4', 2, FALSE);
65INSERT INTO PROD_VARIANT (ID, NAME, PROD_ID, ACTIVE) VALUES (5, 'VAR 5', 3, FALSE);
66INSERT INTO PROD_VARIANT (ID, NAME, PROD_ID, ACTIVE) VALUES (6, 'VAR 6', 3, FALSE);
67
68SELECT
69  *
70FROM
71  CAT C
72JOIN
73  PROD_CAT PC ON PC.CAT_ID = C.ID
74JOIN
75    PROD P ON P.ID = PC.PROD_ID
76JOIN
77    PROD_VARIANT PV ON PV.PROD_ID = P.ID
78WHERE
79    PV.ACTIVE = TRUE
80;