On 31 October 2016 at 12:53, Alexander Farber <alexander.farber@xxxxxxxxx> wrote: > > Good afternoon, > > is it please posible to optimize the following SQL query with numerous CASE statements (on same condition!) without switching to PL/pgSQL? You could break the game table apart into game and gameplayer. That's more "normal" and fits much more nicely, IMO, and you could then resolve the CASE by using joins between game and (twice) gameplayer: SELECT ... FROM game INNER JOIN gameplayer AS myplayer ON game.gameid=myplayer.gameid AND myplayer.uid=in_uid INNER JOIN gameplayer AS otherplayer ON game.gameid=otherplayer.gameid AND otherplayer.uid!=in_uid ... Then all the other tables simply join to myplayer and otherplayer. If you have to stick with the designed schema, you could probably do something with arrays, but I doubt if that would get any less messy and certainly no more readable. FWIW you can resolve half of the CASEs by resolving it in the join to s1 and s2 - so LEFT JOIN words_social s1 ON s1.uid = in_uid LEFT JOIN words_social s2 ON CASE WHEN g.player1 = in_uid THEN g.player2 ELSE g.player1 etc Geoff -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
