0

I have two tables

CREATE TABLE REFERENCE_VALUES
(
REFERENCE_ID SERIAL,
REFERENCE_OBJ_NAME TEXT,
REFERENCE_VALUE_CODE BIGINT,
DISPLAY_NAME TEXT,
CREATED_ON TIMESTAMP,
MODIFIED_ON TIMESTAMP
);

ALTER TABLE REFERENCE_VALUES
ADD PRIMARY KEY (ID);

ALTER TABLE REFERENCE_VALUES
ADD FOREIGN KEY (REFERENCE_VALUE_CODE)
REFERENCES CATEGORY(ID);

CREATE TABLE CATEGORY
(
ID BIGSERIAL NOT NULL,
CODE TEXT NOT NULL,
NAME TEXT NOT NULL,
PARENT_ID BIGINT,
PATH TEXT,
COMP_ID BIGINT,
CREATED_ON TIMESTAMP,
MODIFIED_ON TIMESTAMP,
);

ALTER TABLE CATEGORY
ADD PRIMARY KEY (ID);

ALTER TABLE CATEGORY
ADD FOREIGN KEY (COMP_ID)
REFERENCES COMPANY_ID(ID);

I have the display names as DISPLAY_NAME in REFERENCE_VALUES table I want to SELECT all values from table CATEGORY but the value of NAME (In CATEGORY table) replaced by value from DISPLAY_NAME in REFERENCE_VALUES and if the value of of DISPLAY_NAME is NULL OR EMPTY i would keep the value of NAME (CATEGORY table).

I have been able to do that with the query below

SELECT C.ID, C.CODE, COALESCE(R.DISPLAY_NAME, C.NAME) as NAME, C.PARENT_ID, C.PATH, C.COMP_ID, C.CREATED_ON, C.MODIFIED_ON 
FROM CATEGORY C
LEFT JOIN REFERENCE_VALUES R
ON C.ID = R.REFERENCE_VALUE_CODE
WHERE R.REFERENCE_OBJ_NAME = 'CATEGORY';

but i am getting only two records. How can i get all the records from the category table?

sample data to populate the tables

INSERT INTO CATEGORY VALUES (1, 'CVB', 'COMM VEH', NULL, 'CVB', 1, '2016-05-13 15:50:19.985', NULL);
INSERT INTO CATEGORY VALUES (2, 'LVB', 'AUTO', NULL, 'LVB', 1, '2016-05-13 15:50:19.994', NULL);
INSERT INTO CATEGORY VALUES (3, 'INB', 'INF', NULL, 'INB', 1, '2016-05-13 15:50:19.997', NULL);
INSERT INTO CATEGORY VALUES (4, 'OHB', 'OFF', NULL, 'OHB', 1, '2016-05-13 15:50:20', NULL);
INSERT INTO CATEGORY VALUES (5, 'LUB', 'LUB', NULL, 'LUB', 1, '2016-05-13 15:50:20.002', NULL);
INSERT INTO CATEGORY VALUES (52, 'TRA', 'TIE', 32, 'CVB.HA.TRA', 1, '2016-05-13 15:51:32.605', NULL);
INSERT INTO CATEGORY VALUES (68, 'PF', 'PER', 42, 'LVB.LA.PF', 1, '2016-05-13 15:51:33.117', NULL);
INSERT INTO CATEGORY VALUES (73, 'CE', 'CAR', 32, 'CVB.HA.CE', 1, '2016-05-13 15:51:33.733', NULL);
INSERT INTO CATEGORY VALUES (74, 'KP', 'KP', 32, 'CVB.HA.KP', 1, '2016-05-13 15:51:33.958', NULL);
INSERT INTO CATEGORY VALUES (26, 'RP', 'RING', 11, 'OHB.OH.RP', 1, '2016-05-13 15:51:30.149', NULL);
INSERT INTO CATEGORY VALUES (47, 'CP', 'COMP', 9, 'CVB.CV.CP', 1, '2016-05-13 15:51:31.903', NULL);
INSERT INTO CATEGORY VALUES (48, 'TB', 'TUB', 9, 'CVB.CV.TB', 1, '2016-05-13 15:51:31.905', NULL);
INSERT INTO CATEGORY VALUES (18, 'FB', 'FILT', 11, 'OHB.OH.FB', 1, '2016-05-13 15:51:30.002', NULL);

INSERT INTO REFERENCE_VALUES (ID, REFERENCE_OBJ_NAME, REFERENCE_VALUE_CODE, DISPLAY_NAME) VALUES (1, 'CATEGORY', 6, INDU CHANGED);
INSERT INTO REFERENCE_VALUES (ID, REFERENCE_OBJ_NAME, REFERENCE_VALUE_CODE) VALUES (2, 'CATEGORY', 7);
2
  • 1
    move the where condition to the join clause. Commented May 27, 2016 at 13:46
  • 2
    The WHERE clause is applied after the join. When there is no matching record in R the value of R.REFERENCE_OBJ_NAME is NULL and so your WHERE clause then filters the row out. You just turned your LEFT JOIN back in to an INNER JOIN. Instead, you want the condition to be applied during the join; ON C.ID = R.REFERENCE_VALUE_CODE AND R.REFERENCE_OBJ_NAME = 'CATEGORY' Commented May 27, 2016 at 13:48

1 Answer 1

1

The WHERE condition filters out rows where R.REFERENCE_OBJ_NAME is null, which is all rows for which there are no matching REFERENCE_VALUE.

Maybe what you are trying to do is only join REFERENCE_VALUES where REFERENCE_OBJ_NAME is 'CATEGORY'. If so you could do it like this:

SELECT C.ID, C.CODE, COALESCE(R.DISPLAY_NAME, C.NAME) as NAME, C.PARENT_ID, C.PATH, C.COMP_ID, C.CREATED_ON, C.MODIFIED_ON 
FROM CATEGORY C
LEFT JOIN REFERENCE_VALUES R
ON R.REFERENCE_OBJ_NAME = 'CATEGORY' AND C.ID = R.REFERENCE_VALUE_CODE
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.