CODEWITHSUNDEEP
sqlpostgresql
zemirco
zemirco

Reputation: 16395

Find all matches for given team in PostgreSQL

I've to a table matches. Matches have a name and a uuid for both teams (team_a_uuid and team_b_uuid) that play against each other.

uuid | name | team_a_uuid | team_b_uuid
-----|------|-------------|------------
d7e..| foo  | 5db46a15-...| 82732895-..
334..| bar  | 75ab1a39-...| 9fcedf80-..
...  | ...  | ...         | ...

I've got a second table teams.

uuid | player_1_uuid | player_2_uuid
-----|---------------|--------------
729..| f432f7bc-63...| e022ccb6-7...
d0f..| c9548a8e-b7...| a28441cb-2...
...  | ...           | ...

Here is what I'd like to get: Give me a team and the match uuids it has played in

uuid | player_1_uuid | player_2_uuid | match_uuids
-----|---------------|---------------|---------------------
729..| f432f7bc-63...| e022ccb6-7... | {'d7e...', '334...'}
d0f..| c9548a8e-b7...| a28441cb-2... | {'abc...', 'def...'}

I'm really stuck here. Cheers!

Upvotes: 1

Views: 98

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270191

The key is array_agg(). You can do this either with an explicit join or a correlated subquery:

select t.*,
       (select array_agg(m.uuid)
        from matches m
        where t.uuid in (m.team_a_uuid, m.team_b_uuid)
       ) as match_uuids
from teams t;

Upvotes: 1

Sandrine R.
Sandrine R.

Reputation: 56

SELECT t.uuid, t.player_1_uuid, t.player_2_uuid, array_agg(m.uuid) as "match_uuids"
FROM teams t JOIN matches m ON t.uuid IN (m.team_a_uuid, m.team_b_uuid)
GROUP BY t.uuid;

Upvotes: 3

Related Questions