CODEWITHSUNDEEP
sqlpostgresql
zemirco
zemirco

Reputation: 16395

PostgreSQL Find all matches for given player

This is a follow-up question to my last one Find all matches for given team in PostgreSQL.

Here is my data structure. I've got players

uuid | first_name | last_name
-----| -----------| ---------
...  | hard       | hitter
...  | big        | blocker
...  | super      | setter

Two players form a team in my teams table

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

And two teams play against each other in a match stored in my matches table.

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

In my previous question I asked about getting a team and all the match uuids it has played in. Here is one solution

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;

Now I'd like to go one step further: Give me a player and the match uuids it has played in. It's a bit more complicated because we have to get the teams first and then the matches. I'd like to get something like this

uuid | first_name | last_name | team_uuids    | match_uuids
-----| -----------| ----------|---------------|---------------
...  | hard       | hitter    | {'...', '...'}| {'...', '...'}
...  | big        | blocker   | {'...', '...'}| {'...', '...'}
...  | super      | setter    | {'...', '...'}| {'...', '...'}

Any ideas?

Upvotes: 0

Views: 267

Answers (1)

Adam
Adam

Reputation: 5599

I couldn't actually run the query (no real tables to test) but it should be something like this:

SELECT
    players.uuid,
    players.first_name,
    players.last_name,
    array_agg(teams.uuid) AS team_uuids,
    array_agg(matches.uuid) AS match_uuids
FROM
    players
    JOIN teams
    ON (players.uuid = teams.player_1_uuid OR players.uuid = teams.player_2_uuid)
    JOIN matches
    ON (teams.uuid = matches.team_a_uuid OR teams.uuid = matches.team_b_uuid)
GROUP BY
    players.uuid,
    players.first_name,
    players.last_name;

I suppose, that in the GROUP BY clause the players.uuid column is enough if it is the primary key - you can try to remove the players.first_name and players.last_name columns.

Upvotes: 1

Related Questions