From: | Alexander Farber <alexander(dot)farber(at)gmail(dot)com> |
---|---|
To: | |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Multiple records returned by a JOIN |
Date: | 2018-04-11 10:44:12 |
Message-ID: | CAADeyWj-m5OYMXwWs3Z5AJg2qBzrLA=OfeS8rAAnptNPjGGaHw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Last night I have inexplicably missed 2 conditions /facepalm
Now my JOIN works ok, without multiple records -
CREATE OR REPLACE FUNCTION words_stat_games(
in_social integer,
in_sid text
) RETURNS TABLE (
out_gid integer,
out_reason text,
out_state1 text,
out_score1 integer,
out_score2 integer
) AS
$func$
SELECT
g.gid,
g.reason,
CASE WHEN g.player1 = s.uid THEN g.state1 ELSE g.state2 END,
CASE WHEN g.player1 = s.uid THEN g.score1 ELSE g.score2 END,
CASE WHEN g.player1 = s.uid THEN g.score2 ELSE g.score1 END
FROM words_games g
JOIN words_social s ON (g.player1 = s.uid OR g.player2 = s.uid)
WHERE g.finished IS NOT NULL
AND s.social = in_social -- MISSED
CONDITION
AND s.sid = in_sid -- MISSED
CONDITION
ORDER BY g.finished DESC
LIMIT 10;
$func$ LANGUAGE sql STABLE;
From | Date | Subject | |
---|---|---|---|
Next Message | Laurenz Albe | 2018-04-11 11:01:19 | Re: Planning to change autovacuum_vacuum_scale_factor value to zero. Please suggest me if any negative impact. |
Previous Message | Raghavendra Rao J S V | 2018-04-11 10:00:10 | Re: Planning to change autovacuum_vacuum_scale_factor value to zero. Please suggest me if any negative impact. |