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, '109998440415755555271'); >> 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? >> >> > JOIN words_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
