sql - Why doesn't this left outer join query work properly? -

i have table looks this:

peter=> \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) 

and table looks this:

peter=> \d months    table "pg_temp_2.months"   column   | type | modifiers  -----------+------+-----------  xtn_month | date |  

months contains this:

 xtn_month    ------------  2011-01-01   2011-02-01   2011-03-01   2011-04-01   2011-05-01   2011-06-01   2011-07-01  

aggregated_accounts_by_month contains relevant data:

 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 

the query i'm trying run is:

select      months.xtn_month,     account,     amount     aggregated_accounts_by_month     left outer join months on months.xtn_month = a.xtn_month     account = 'expenses:fuel' order     xtn_month; 

what want query give me these results:

 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 

but it's giving me this:

 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 

i'm doing wrong. ideas? i'm running postgresql 9.0.4 on mac os x 10.6.7.

edit: after thinking more, need left outer join against not months, against accounts. query want:

select     xtn_month,     account,     coalesce(amount, 0)     (         select             xtn_month,             account                 (             select                 distinct xtn_month                             aggregated_accounts_by_month         ) x         cross join         (             select                 distinct account                             aggregated_accounts_by_month         ) y     ) z     left outer join aggregated_accounts_by_month         using (xtn_month, account)     account = 'expenses:fuel' order     xtn_month; 

ypercube's answer almost right, except didn't fill in account column. query of course rather expensive, cross product in there. that's ok, though, because aggregated_accounts_by_month has little under 2000 rows on 4 years of data.

two things:

• reverse order of 2 tables in left join and
• move condition where on clause.

.

select      months.xtn_month,     a.account,     a.amount     months      left outer join aggregated_accounts_by_month         on  months.xtn_month = a.xtn_month         , a.account = 'expenses:fuel' order     xtn_month;