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.johnston@xxxxxxxxx> 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
