title: Why doesn't this left outer join query work properly?
body: <p>I have a table that looks like this:</p>
  
  <pre><code>peter=&gt; \d aggregated_accounts_by_month
  Table "public.aggregated_accounts_by_month"
    Column   |  Type   | Modifiers 
  -----------+---------+-----------
   xtn_month | date    | 
   account   | text    | 
   commodity | text    | 
   amount    | numeric | 
  Indexes:
      "idx_aggregated_accounts_by_month_account" btree (account)
      "idx_aggregated_accounts_by_month_month" btree (xtn_month)
  </code></pre>
  
  <p>And another table that looks like this:</p>
  
  <pre><code>peter=&gt; \d months
     Table "pg_temp_2.months"
    Column   | Type | Modifiers 
  -----------+------+-----------
   xtn_month | date | 
  </code></pre>
  
  <p><code>months</code> contains this:</p>
  
  <pre><code> xtn_month   
  ------------
   2011-01-01 
   2011-02-01 
   2011-03-01 
   2011-04-01 
   2011-05-01 
   2011-06-01 
   2011-07-01 
  </code></pre>
  
  <p><code>aggregated_accounts_by_month</code> contains this relevant data:</p>
  
  <pre><code> xtn_month  |    account    | amount 
  ------------+---------------+--------
   2011-01-01 | Expenses:Fuel | 111.31
   2011-02-01 | Expenses:Fuel |  89.29
   2011-03-01 | Expenses:Fuel |  97.41
   2011-04-01 | Expenses:Fuel | 101.70
   2011-05-01 | Expenses:Fuel |   52.9
   2011-07-01 | Expenses:Fuel |  49.55
  </code></pre>
  
  <p>The query I'm trying to run is:</p>
  
  <pre><code>select 
      months.xtn_month,
      account,
      amount
  from
      aggregated_accounts_by_month a
      left outer join months on months.xtn_month = a.xtn_month
  where
      account = 'Expenses:Fuel'
  order by
      xtn_month;
  </code></pre>
  
  <p>What I want this query to do is give me these results:</p>
  
  <pre><code> xtn_month  |    account    | amount 
  ------------+---------------+--------
   2011-01-01 | Expenses:Fuel | 111.31
   2011-02-01 | Expenses:Fuel |  89.29
   2011-03-01 | Expenses:Fuel |  97.41
   2011-04-01 | Expenses:Fuel | 101.70
   2011-05-01 | Expenses:Fuel |   52.9
   2011-06-01 | Expenses:Fuel |
   2011-07-01 | Expenses:Fuel |  49.55
  </code></pre>
  
  <p>But it's actually giving me this:</p>
  
  <pre><code> xtn_month  |    account    | amount 
  ------------+---------------+--------
   2011-01-01 | Expenses:Fuel | 111.31
   2011-02-01 | Expenses:Fuel |  89.29
   2011-03-01 | Expenses:Fuel |  97.41
   2011-04-01 | Expenses:Fuel | 101.70
   2011-05-01 | Expenses:Fuel |   52.9
   2011-07-01 | Expenses:Fuel |  49.55
  </code></pre>
  
  <p>I'm clearly doing something wrong. Any ideas? I'm running PostgreSQL 9.0.4 on Mac OS X 10.6.7.</p>
  
  <p><em>Edit:</em> After thinking about this some more, I need to left outer join against not only months, but also against accounts. This query does exactly what I want: </p>
  
  <pre><code>select
      xtn_month,
      account,
      coalesce(amount, 0)
  from
      (
          select
              xtn_month,
              account
          from
          (
              select
                  distinct xtn_month
              from
                  aggregated_accounts_by_month
          ) x
          cross join
          (
              select
                  distinct account
              from
                  aggregated_accounts_by_month
          ) y
      ) z
      left outer join aggregated_accounts_by_month
          using (xtn_month, account)
  where
      account = 'Expenses:Fuel'
  order by
      xtn_month;
  </code></pre>
  
  <p>ypercube's answer was <em>almost</em> right, except that it didn't fill in the <code>account</code> column. This query is of course rather expensive, what with that cross product in there. That's ok, though, because <code>aggregated_accounts_by_month</code> has a little under 2000 rows for over four years of data.</p>