Re: Multiple records returned by a JOIN

On Tue, Apr 10, 2018 at 8:44 AM, Alexander Farber <
alexander(dot)farber(at)gmail(dot)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.

David J.