Search Postgresql Archives

Re: Multiple records returned by a JOIN

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

David J.


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux