postgresql外连接查询从一个表中获取所有数据(postgresql outer join query to get all data from one table)
我有两张桌子
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);我在REFERENCE_VALUES表中显示名称为DISPLAY_NAME我要从表CATEGORY中选择所有值，但是NAME（在CATEGORY表中）的值被REFERENCE_VALUES中DISPLAY_NAME的值替换，如果DISPLAY_NAME的值为NULL或者我将保留EMPTY NAME（CATEGORY表）的值。
我已经能够通过下面的查询来做到这一点
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';但我只得到两个记录。 如何从类别表中获取所有记录？
填充表格的示例数据
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);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);最满意答案
WHERE条件筛选出R.REFERENCE_OBJ_NAME为空的行，这是所有没有匹配REFERENCE_VALUE的行。
也许你想要做的只是加入REFERENCE_VALUES，其中REFERENCE_OBJ_NAME是'CATEGORY'。 如果是这样，你可以这样做：
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_CODEThe 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_CODEpostgresql外连接查询从一个表中获取所有数据(postgresql outer join query to get all data from one table)我有两张桌子
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);我在REFERENCE_VALUES表中显示名称为DISPLAY_NAME我要从表CATEGORY中选择所有值，但是NAME（在CATEGORY表中）的值被REFERENCE_VALUES中DISPLAY_NAME的值替换，如果DISPLAY_NAME的值为NULL或者我将保留EMPTY NAME（CATEGORY表）的值。
我已经能够通过下面的查询来做到这一点
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';但我只得到两个记录。 如何从类别表中获取所有记录？
填充表格的示例数据
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);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);最满意答案
WHERE条件筛选出R.REFERENCE_OBJ_NAME为空的行，这是所有没有匹配REFERENCE_VALUE的行。
也许你想要做的只是加入REFERENCE_VALUES，其中REFERENCE_OBJ_NAME是'CATEGORY'。 如果是这样，你可以这样做：
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_CODEThe 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
发布评论