I have this set of tables and data
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 ); I want to get a result set:
name value_1 value_2 --------------------------- item1 val1 <null> item2 <null> val2 and I use the query:
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 ) The question is: How can I avoid nested selects?
I'm using Postgresql
