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(dot)g(dot)johnston(at)gmail(dot)com> wrote:

> 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.
>
>
I don't know why this triple is there, but it really is :-)

Regards
Alex