PostgreSQL: left outer join syntax

Question

I'm using PostgreSQL 8.4.6 with CentOS 5.5 and have a table of users:

# 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 a game each week:

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

Then I'm trying to display both of these infos for a 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've got no rows, because the user 'DE2' hasn't earned any "virtual money" this week yet.

I'd like to change my query so that it always returns data for existing users and if they haven't played this week - then the query should return 0.

So I guess 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;

but these returns me a lot of rows with different users instead of just one with the id='DE2'.

What am I doing wrong please?

Daniel · Accepted Answer · 2011-01-02 19:09:58Z

5

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;

answered Jan 2, 2011 at 19:09

Daniel

28.2k20 gold badges93 silver badges140 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Daniel Over a year ago

EDIT: added the m.yw back into the on-clause

araqnid · Accepted Answer · 2011-01-02 19:09:40Z

1

You want to put the filter condition u.id='DE2' in the WHERE clause, leaving everything else in the ON clause. For every user where the ON doesn't match, it will still output the row with the data from u and nulls for m.

answered Jan 2, 2011 at 19:09

araqnid

135k25 gold badges164 silver badges139 bronze badges

Comments

escargot agile · Accepted Answer · 2011-01-02 19:10:00Z

1

Try moving the

u.id='DE2'

into a WHERE clause, like this:

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;

answered Jan 2, 2011 at 19:10

escargot agile

22.4k15 gold badges88 silver badges144 bronze badges

Collectives™ on Stack Overflow

PostgreSQL: left outer join syntax

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related