多个联接到同一个表 [英] Multiple joins to same table

问题描述

我有这组表和数据

CREATE TABLE item (  
    id INT PRIMARY KEY,  
    name VARCHAR  
);  
CREATE TABLE property (  
    id INT PRIMARY KEY,  
    name VARCHAR  
);  
CREATE TABLE value (  
    id INT PRIMARY KEY,  
    value VARCHAR  
);  
CREATE TABLE item_value (  
    item INT NOT NULL REFERENCES item(id),  
    property INT NOT NULL REFERENCES property(id),  
    value INT NOT NULL REFERENCES value(id)  
);  
INSERT INTO item (id, name) VALUES (1, 'item1'), (2, 'item2');  
INSERT INTO property (id, name) VALUES (1, 'prop1'), (2, 'prop2');  
INSERT INTO value (id, value) VALUES (1, 'val1'), (2, 'val2');  
INSERT INTO item_value (item, property, value) VALUES (1, 1, 1), (2,2,2 );  

我想要一个结果集:

name    value_1    value_2
---------------------------  
item1   val1       <null>
item2   <null>     val2

我使用查询:

SELECT i.name as name, v1.value as value_1, v2.value as value_2 
FROM item i
LEFT JOIN item_value iv ON iv.item = i.id
LEFT JOIN value v1 ON v1.id = iv.value AND v1.id = (
  SELECT v.id FROM value v 
  JOIN property p ON p.id = iv.property 
    AND p.name = 'prop1' AND v.id = v1.id AND v.id = iv.value
)
LEFT JOIN value v2 ON v2.id = iv.value AND v2.id = (
  SELECT v.id FROM value v 
  JOIN property p ON p.id = iv.property 
    AND p.name = 'prop2' AND v.id = v2.id AND v.id = iv.value
)

问题是:如何避免嵌套选择?
我正在使用Postgresql

The question is: How can I avoid nested selects?
I'm using Postgresql

推荐答案

您可以尝试

SELECT i.name as name, v1.value as value_1, v2.value as value_2 
  FROM item i
       INNER JOIN item_value iv ON iv.item = i.id
       INNER JOIN property p ON iv.property = p.id
       LEFT JOIN value v1 ON p.name = 'prop1' AND v1.id = iv.value
       LEFT JOIn value v2 ON p.name = 'prop2' AND v2.id = iv.value

这篇关于多个联接到同一个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！