title: PostgreSQL LEFT OUTER JOIN query syntax
body: <p>Lets say I have a <code>table1</code>:</p>
  
  <pre><code>  id      name
  -------------
    1       "one"
    2       "two"
    3       "three"
  </code></pre>
  
  <p>And a <code>table2</code> with a foreign key to the first:</p>
  
  <pre><code>id    tbl1_fk    option   value
  -------------------------------
   1      1         1        1
   2      2         1        1
   3      1         2        1
   4      3         2        1
  </code></pre>
  
  <p>Now I want to have as a query result:</p>
  
  <pre><code>table1.id | table1.name | option | value
  -------------------------------------
        1       "one"        1       1
        2       "two"        1       1
        3       "three"    
        1       "one"        2       1
        2       "two"    
        3       "three"      2       1
  </code></pre>
  
  <p>How do I achieve that?</p>
  
  <p>I already tried:</p>
  
  <pre><code>SELECT
    table1.id,
    table1.name,
    table2.option,
    table2.value
  FROM table1 AS table1
  LEFT outer JOIN table2 AS table2 ON table1.id = table2.tbl1fk
  </code></pre>
  
  <p>but the result seems to omit the null vales:</p>
  
  <pre><code>1    "one"    1   1
  2    "two"    1   1
  1    "one"    2   1
  3    "three"  2   1
  </code></pre>
  
  <p>SOLVED: thanks to Mahmoud Gamal: (plus the GROUP BY)
  Solved with this query</p>
  
  <pre><code>SELECT 
    t1.id,
    t1.name,
    t2.option,
    t2.value
  FROM
  (
    SELECT t1.id, t1.name, t2.option
    FROM table1 AS t1
    CROSS JOIN table2 AS t2
  ) AS t1
  LEFT JOIN table2 AS t2  ON t1.id = t2.tbl1fk
                         AND t1.option = t2.option
  group by t1.id, t1.name, t2.option, t2.value
  ORDER BY t1.id, t1.name
  </code></pre>