Hello,
I have an 8.4.9 table, where users can
assess other users (i.e. "nice" vs. "not nice"):
# \d pref_rep
Table "public.pref_rep"
Column | Type | Modifiers
------------+-----------------------------+-----------------------------------------------------------
id | character varying(32) |
nice | boolean |
and then another table with purchased VIP-status
as timestamp (can be NULL if never purchased):
# \d pref_users
Table "public.pref_users"
Column | Type | Modifiers
------------+-----------------------------+---------------
id | character varying(32) | not null
vip | timestamp without time zone |
As a Xmas present (russian server,
thus different Xmas date :-) I'd like to award
a week of "VIP-status" to all "nice" users.
I can fetch a list of "nice" users here:
# select r.id, count(nullif(r.nice, false)) - count(nullif(r.nice, true))
from pref_rep r group by r.id
having count(nullif(r.nice, false))-count(nullif(r.nice, true)) > 0;
-------------------------+----------
DE10011 | 2
DE10016 | 35
DE10095 | 79
But when I try to join it with pref_users table:
# select r.id, u.vip, count(nullif(r.nice, false)) - count(nullif(r.nice, true))
from pref_rep r, pref_users u group by r.id
having r.id=u.id and count(nullif(r.nice, false))-count(nullif(r.nice,
true)) > 0;
ERROR: column "u.vip" must appear in the GROUP BY clause or be used
in an aggregate function
LINE 1: select r.id, u.vip, count(nullif(r.nice, false)) - count(nul...
What to do? (besides stopping to harass users :-)
Regards
Alex