多个连接到同一个表

Question

我有这组表和数据，

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

Answer 1

你可以尝试使用

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