Re: Multiple records returned by a JOIN
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_sidtext ) RETURNS TABLE ( out_gidinteger, 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 FROMwords_games g JOINwords_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;
Re: Multiple records returned by a JOIN
Or actually I can not use SELECT UNION here, because then I only get 10 records of the condition uid = player1 and then nothing would be left for the other condition uid = player2
Re: Multiple records returned by a JOIN
Ok thanks, I guess I should switch to a SELECT UNION (first on uid = player1 and the uid = player2) and that will fix the CASE ... END for me. On Tue, Apr 10, 2018 at 6:01 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Tue, Apr 10, 2018 at 8:44 AM, Alexander Farber < > alexander.far...@gmail.com> wrote: > >> Unfortunately, it returns multiple records and with wrong values too: >> >> # select * from words_stat_games(1, '10999844041575271'); >> out_gid | out_reason | out_state1 | out_score1 | out_score2 >> -++++ >> 1978 | resigned | lost | 0 | 0 >> 1978 | resigned | won| 0 | 0 >> 1847 | resigned | lost |234 |441 >> 1847 | resigned | won|441 |234 >> 1847 | resigned | won|441 |234 >> 1800 | expired| won| 41 | 0 >> 1798 | expired| lost | 8 | 28 >> 1798 | expired| won| 28 | 8 >> 1800 | expired| lost | 0 | 41 >> 1926 | expired| won| 35 | 13 >> (10 rows) >> >> Why does it return the game 1978 twice and also the out_state1 changes >> between 'lost' and 'won' values? >> >> > JOINwords_social s ON (g.player1 = s.uid OR g.player2 = s.uid) > > I'm more surprised by the single and triple than the doubles. Your join > against social, which has a record for each user, and games which has two > users, should result in an output with two rows, one for each of the users > in the games row. One of those users wins, and one of them loses. How you > have 2 winners in 1847 I cannot tell without seeing data. Why there is no > loser for 1926 is likewise a mystery. > > I don't know why this triple is there, but it really is :-) Regards Alex
Re: Multiple records returned by a JOIN
On Tue, Apr 10, 2018 at 8:44 AM, Alexander Farber < alexander.far...@gmail.com> wrote: > Unfortunately, it returns multiple records and with wrong values too: > > # select * from words_stat_games(1, '10999844041575271'); > out_gid | out_reason | out_state1 | out_score1 | out_score2 > -++++ > 1978 | resigned | lost | 0 | 0 > 1978 | resigned | won| 0 | 0 > 1847 | resigned | lost |234 |441 > 1847 | resigned | won|441 |234 > 1847 | resigned | won|441 |234 > 1800 | expired| won| 41 | 0 > 1798 | expired| lost | 8 | 28 > 1798 | expired| won| 28 | 8 > 1800 | expired| lost | 0 | 41 > 1926 | expired| won| 35 | 13 > (10 rows) > > Why does it return the game 1978 twice and also the out_state1 changes > between 'lost' and 'won' values? > > JOINwords_social s ON (g.player1 = s.uid OR g.player2 = s.uid) I'm more surprised by the single and triple than the doubles. Your join against social, which has a record for each user, and games which has two users, should result in an output with two rows, one for each of the users in the games row. One of those users wins, and one of them loses. How you have 2 winners in 1847 I cannot tell without seeing data. Why there is no loser for 1926 is likewise a mystery. David J.
Multiple records returned by a JOIN
Good evening, in PostgreSQL 10.3 I have written the following custom function (trying to fetch 10 latest games played by a user): CREATE OR REPLACE FUNCTION words_stat_games( in_social integer, in_sidtext ) RETURNS TABLE ( out_gidinteger, 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 FROMwords_games g JOINwords_social s ON (g.player1 = s.uid OR g.player2 = s.uid) WHERE g.finished IS NOT NULL ORDER BY g.finished DESC LIMIT 10; $func$ LANGUAGE sql STABLE; Unfortunately, it returns multiple records and with wrong values too: # select * from words_stat_games(1, '10999844041575271'); out_gid | out_reason | out_state1 | out_score1 | out_score2 -++++ 1978 | resigned | lost | 0 | 0 1978 | resigned | won| 0 | 0 1847 | resigned | lost |234 |441 1847 | resigned | won|441 |234 1847 | resigned | won|441 |234 1800 | expired| won| 41 | 0 1798 | expired| lost | 8 | 28 1798 | expired| won| 28 | 8 1800 | expired| lost | 0 | 41 1926 | expired| won| 35 | 13 (10 rows) Why does it return the game 1978 twice and also the out_state1 changes between 'lost' and 'won' values? I hoped to handle that with my "CASE WHEN g.player1 = s.uid THEN g.state1 ELSE g.state2 END", but it obviously fails Below are my 2 table definitions, thank you for any hints. CREATE TABLE words_social ( sid text NOT NULL, social integer NOT NULL CHECK (0 < social AND social <= 64), uid integer NOT NULL REFERENCES words_users ON DELETE CASCADE, PRIMARY KEY(sid, social) ) CREATE TABLE words_games ( gid SERIAL PRIMARY KEY, created timestamptz NOT NULL, finished timestamptz, player1 integer REFERENCES words_users(uid) ON DELETE CASCADE NOT NULL CHECK (player1 <> player2), player2 integer REFERENCES words_users(uid) ON DELETE CASCADE, reason text, -- regular, resigned, expired, banned state1 text, -- tie, winning, losing, draw, won, lost state2 text, -- tie, winning, losing, draw, won, lost score1 integer NOT NULL CHECK (score1 >= 0), score2 integer NOT NULL CHECK (score2 >= 0) ); CREATE INDEX words_games_state1_index ON words_games(state1); CREATE INDEX words_games_state2_index ON words_games(state2); CREATE INDEX words_games_reason_index ON words_games(reason);
