Re: STRING_AGG and GROUP BY

On Fri, Mar 16, 2018 at 9:10 AM, Alexander Farber <
alexander(dot)farber(at)gmail(dot)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.