PostgreSQL: left outer join syntax

Question

I am using PostgreSQL 8.4.6 with CentOS 5.5 and have a user table:

# select * from pref_users where id='DE2';
 id  | first_name | last_name | female |      avatar      |        city         | lat | lng |           login            | last_ip | medals |           logout
-----+------------+-----------+--------+------------------+---------------------+-----+-----+----------------------------+---------+--------+----------------------------
 DE2 | Alex       |           | f      | 2_1280837766.jpg | .    |     |     | 2011-01-02 19:26:37.790909 |         |        | 2011-01-02 19:29:30.197062
(1 row)

and another table listing their "virtual money" won in the game every week:

# select * from pref_money where id='DE2';
 id  | money |   yw
-----+-------+---------
 DE2 |    66 | 2010-48
(1 row)

Then I try to display both of these information for the user, but I'm only interested in the user's money for the current week:

# select u.id,
    u.first_name,
    u.city,
    u.avatar,
    m.money,
    u.login > u.logout as online
from pref_users u, pref_money m where
    m.yw=to_char(current_timestamp, 'YYYY-IW') and
    u.id=m.id and
    u.id='DE2'
order by m.money desc;
 id | first_name | city | avatar | money | online
----+------------+------+--------+-------+--------
(0 rows)

In this case, I have no lines, because the user "DE2" has not yet received any "virtual money" this week.

I would like to modify my query so that it always returns data for existing users , and if they have not played this week, then the query should return 0.

So, I need an outer left join , and I'm trying:

 select u.id,
    u.first_name,
    u.city,
    u.avatar,
    m.money,
    u.login > u.logout as online
from pref_users u left outer join pref_money m on (
    m.yw=to_char(current_timestamp, 'YYYY-IW') and
    u.id=m.id and
    u.id='DE2')
order by m.money desc;

, id = 'DE2'.

?

Answer 1

select u.id,
       u.first_name,
       u.city,
       u.avatar,
       coalesce(m.money,0),
       u.login > u.logout as online
  from pref_users u left outer join pref_money m on u.id=m.id
       and m.yw=to_char(current_timestamp, 'YYYY-IW') 
 where u.id='DE2'
 order by m.money desc;

Answer 2

u.id='DE2' WHERE, ON. , ON , u nulls m.

Answer 3

Try moving

u.id='DE2'

in the WHERE clause, for example:

select u.id,
    u.first_name,
    u.city,
    u.avatar,
    m.money,
    u.login > u.logout as online
from pref_users u left outer join pref_money m on u.id=m.id
where m.yw=to_char(current_timestamp, 'YYYY-IW') and
    u.id='DE2'
order by m.money desc;