On Fri, Mar 16, 2018 at 9:10 AM, Alexander Farber < alexander.far...@gmail.com> wrote:
> > But you say that "tiles" and "word (score)" are unrelated and this does > not seem true to me: > > For each move id aka "mid" there is a JSON value, describing how the > player played the letter tiles. > And for the same "mid" there is a list of one or more "word (score)"s > achieved... > For each mid you want to know all tiles played and all word scores achieved - but you want to forget/ignore that a given tile achieved a given word score. IOW, you are intentionally forgetting/ignoring the fact that the tiles and the corresponding word scores are related to each other, beyond the simple/incomplete relationship that both share the same mid. You thus need to write a query that only relates tiles and word scores to mid and not to each other. > Maybe it is possible to GROUP BY tiles somehow or some kind of special SQL > JOIN? > > I do not know if the tables or columns below match your model but the concept should still come across intact. SELECT mid, (SELECT string_agg(tiles, '; ') FROM tiles WHERE ta.mid = moves.mid) AS mid_tiles, (SELECT string_agg(words, '; ') FROM words WHERE words.mid = moves.mid) AS mid_words FROM moves There are other ways to write that that could perform better but the idea holds. David J.
