sample.sql

DROP TABLE IF EXISTS CAT CASCADE;

CREATE TABLE CAT (
  ID   BIGINT,
  NAME CHARACTER VARYING(255) NOT NULL UNIQUE
);

ALTER TABLE CAT
  ADD CONSTRAINT CAT_PK PRIMARY KEY (ID);

DROP TABLE IF EXISTS PROD CASCADE;

CREATE TABLE PROD (
  ID   BIGINT,
  NAME CHARACTER VARYING(255) NOT NULL UNIQUE
);

ALTER TABLE PROD
  ADD CONSTRAINT PROD_PK PRIMARY KEY (ID);

DROP TABLE IF EXISTS PROD_CAT;

CREATE TABLE PROD_CAT (
  PROD_ID BIGINT NOT NULL,
  CAT_ID  BIGINT NOT NULL
);

ALTER TABLE PROD_CAT
  ADD CONSTRAINT PROD_FK FOREIGN KEY (PROD_ID) REFERENCES PROD (ID);
ALTER TABLE PROD_CAT
  ADD CONSTRAINT CAT_FK FOREIGN KEY (CAT_ID) REFERENCES CAT (ID);
ALTER TABLE PROD_CAT
  ADD CONSTRAINT PROD_CAT_UNQ UNIQUE (PROD_ID, CAT_ID);

DROP TABLE IF EXISTS PROD_VARIANT;

CREATE TABLE PROD_VARIANT (
  ID      BIGINT,
  NAME    CHARACTER VARYING(255) NOT NULL UNIQUE,
  PROD_ID BIGINT                 NOT NULL,
  ACTIVE  BOOLEAN
);

ALTER TABLE PROD_VARIANT
  ADD CONSTRAINT PROD_VARIANT_PK PRIMARY KEY (ID);
ALTER TABLE PROD_VARIANT
  ADD CONSTRAINT PROD_FK FOREIGN KEY (PROD_ID) REFERENCES PROD (ID);

INSERT INTO CAT (ID, NAME) VALUES (1, 'CAT 1');
INSERT INTO CAT (ID, NAME) VALUES (2, 'CAT 2');
INSERT INTO CAT (ID, NAME) VALUES (3, 'CAT 3');

INSERT INTO PROD (ID, NAME) VALUES (1, 'PROD 1');
INSERT INTO PROD (ID, NAME) VALUES (2, 'PROD 2');
INSERT INTO PROD (ID, NAME) VALUES (3, 'PROD 3');

INSERT INTO PROD_CAT (PROD_ID, CAT_ID) VALUES (1, 1);
INSERT INTO PROD_CAT (PROD_ID, CAT_ID) VALUES (2, 2);
INSERT INTO PROD_CAT (PROD_ID, CAT_ID) VALUES (3, 3);

INSERT INTO PROD_VARIANT (ID, NAME, PROD_ID, ACTIVE) VALUES (1, 'VAR 1', 1, TRUE);
INSERT INTO PROD_VARIANT (ID, NAME, PROD_ID, ACTIVE) VALUES (2, 'VAR 2', 1, TRUE);
INSERT INTO PROD_VARIANT (ID, NAME, PROD_ID, ACTIVE) VALUES (3, 'VAR 3', 2, TRUE);
INSERT INTO PROD_VARIANT (ID, NAME, PROD_ID, ACTIVE) VALUES (4, 'VAR 4', 2, FALSE);
INSERT INTO PROD_VARIANT (ID, NAME, PROD_ID, ACTIVE) VALUES (5, 'VAR 5', 3, FALSE);
INSERT INTO PROD_VARIANT (ID, NAME, PROD_ID, ACTIVE) VALUES (6, 'VAR 6', 3, FALSE);

SELECT
  *
FROM
  CAT C
JOIN
  PROD_CAT PC ON PC.CAT_ID = C.ID
JOIN
    PROD P ON P.ID = PC.PROD_ID
JOIN
    PROD_VARIANT PV ON PV.PROD_ID = P.ID
WHERE
    PV.ACTIVE = TRUE
;