title: Multiple joins to same table
body: <p>I have this set of tables and data  </p>
  
  <pre><code>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 );  
  </code></pre>
  
  <p>I want to get a result set: </p>
  
  <pre><code>name    value_1    value_2
  ---------------------------  
  item1   val1       &lt;null&gt;
  item2   &lt;null&gt;     val2
  </code></pre>
  
  <p>and I use the query:</p>
  
  <pre><code>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
  )
  </code></pre>
  
  <p>The question is: How can I avoid nested selects?<br>
  I'm using Postgresql</p>