[SQL] Conditional query to find what games players are in & invited to

For each player, show their current games & invites in a "reasonably performant" query.

| PLAYERS | | GAMES |
| ID | Name | | ID | Name |
|------|------| |-----|----------|
| 1 | Abby | | 1 | Baccarat |
| 2 | Billy| | 2 | BlackJack|
| 3 | Cary | | 3 | Craps |
| 4 | Dolly| | 4 | Roulette |
| 5 | Eddy |


Players are invited to a game via a list. The list can be changed at any time so a player may be invited to a game and then dropped from the game in the following list:

|LIST| | INVITE |
| ID | | ID | LIST | PLAYER | GAME |
|----| |----|------|--------|------|
| 1 | | 1 | 1 | 1 | 2 |
| 2 | | 2 | 1 | 2 | 2 |
| 3 | | 3 | 1 | 3 | 2 |
| 4 | | 4 | 1 | 4 | 2 |
| 5 | | 5 | 1 | 5 | 2 |
| 6 | | 6 | 2 | 1 | 2 |
| 7 | | 7 | 2 | 2 | 2 |
| 8 | | 8 | 2 | 3 | 2 |
| 9 | | 9 | 2 | 4 | 2 |
| 10 | 3 | 1 | 4 |
| 11 | 3 | 2 | 4 |
| 12 | 3 | 5 | 4 |
| 13 | 4 | 4 | 1 |
| 14 | 4 | 5 | 1 |
| 15 | 3 | 1 | 4 |
| 16 | 3 | 2 | 4 |
| 17 | 3 | 5 | 4 |
| 18 | 4 | 4 | 4 |
| 19 | 4 | 5 | 4 |
| 20 | 5 | 1 | 4 |
| 21 | 5 | 2 | 4 |
| 22 | 5 | 3 | 4 |
| 23 | 5 | 4 | 4 |
| 24 | 5 | 5 | 4 |
| 25 | 6 | 1 | 3 |
| 26 | 6 | 2 | 3 |
| 27 | 6 | 3 | 3 |
| 28 | 6 | 4 | 3 |


Before a round of a game is played, the list can be updated zero or more times. When the game starts, the round is marked as started and marked finished when complete.

| ROUND | |
| ID | Game | List | State |
|------|------|------|--------|
| 1 | 2 | 1 |FINISHED|
| 2 | 2 | 2 |FINISHED|
| 3 | 2 | 2 |STARTED |
| 4 | 4 | 3 |STARTED |
| 9 | 6 | 3 |STARTED |


A player's finished games:

SELECT * FROM GAMES G
JOIN INVITE I ON I.GAME = G.ID
JOIN PLAYER P ON P.ID = I.PLAYER
JOIN ROUND R ON G.ID = R.GAME
WHERE R.STATE = 'FINISHED'


but I'm struggling the conditional query for the current games and invites. I think the current games query is:

SELECT * FROM GAMES G
JOIN INVITE I ON I.GAME = G.ID
JOIN PLAYER P ON P.ID = I.PLAYER
JOIN ROUND R ON G.ID = R.GAME
WHERE R.STATE = 'STARTED'


and I think the invite portion is:

SELECT * FROM GAMES G
JOIN INVITE I ON I.GAME = G.ID
JOIN PLAYER P ON P.ID = I.PLAYER
WHERE I.LIST = (
SELECT MAX(LL.ID) FROM LIST LL WHERE LL.GAME = G.ID
)


but I'm stumped on combining these and whether this is a "reasonably performant" solution.

Any insights are greatly appreciated.

Continue reading...