Should I be using multiple PostgreSQL queries for this?
<p>I have a couple of tables.</p>

<p><strong>Table 1 (player_main)</strong></p>

<p>This table has a "id" column which is a UUID type and is the primary key.</p>

<p><strong>Table 2 (game_main)</strong></p>

<p>This table also has an "id" column of the UUID type and is a primary key.</p>

<p><strong>Table 3 (game_members)</strong>
This table has a column "member_id" of UUID type which is a primary key and a foreign reference to player_main(id).
There is also an "game_id" column of UUID type which references game_main(id).</p>

<p>My problem is, if a player connects to the server, I want to be able to load up their "game data" by querying the database and receiving all the data to construct their data object. I am given the UUID of the player which is stored in player_main(id). I need to obtain the game_main(id) and a list of all the game member ids that correspond to that game_main(id).</p>

<p>How would I do this? I've attempted to do different types of joins with a where clause to identify the game_members(member_id) but that only returns the row that is correspondent to the member that has just joined, not a column containing all of the members for that game.</p>

<p>Any help is appreciated, thank you.</p>

<p><strong>Edit</strong></p>

<p>I have tried the following query:</p>

<pre><code>SELECT t1.member_id, t2.*
FROM game_members t1
INNER JOIN game_main t2
ON t1.game_id = t2.id
WHERE t1.member_id = &lt;some UID&gt;
</code></pre>

<p>which resulted in 1 row and 2 columns. The columns being "game_members.member_id" and "game_main.id". The value for the first column is the UUID that I specified in the where clause and the value for the second column is the UUID of the game. I was expecting to see 2 rows of data with the same "game_main.id" but with different "game_member.member_id"'s, as I have 2 entries in the same game currently.</p>

<p><strong>Edit 2</strong></p>

<p>As requested, I will provide sample data for my tables as well as the output that I wish to see.</p>

<p>Sample Data:</p>

<p>[player_main]</p>

<pre><code>id                                  
------------------------------------|
863fdf91-86fb-49a7-9232-bcb596e3a86f|
7af64cd7-72a2-410f-9b5c-620127fca0ac|
c7b1952a-b263-470f-9cae-9d5e6d7a8186|
</code></pre>

<p>[game_main]</p>

<pre><code>id
------------------------------------|
dd76c680-5853-40a6-b757-0457d1a7e95f|
ca4f5b1f-0f8c-4f10-969c-464ccf207d9c|
</code></pre>

<p>[game_members]</p>

<pre><code>member_id                           | game_id       
------------------------------------|------------------------------------
863fdf91-86fb-49a7-9232-bcb596e3a86f|dd76c680-5853-40a6-b757-0457d1a7e95f
7af64cd7-72a2-410f-9b5c-620127fca0ac|dd76c680-5853-40a6-b757-0457d1a7e95f
c7b1952a-b263-470f-9cae-9d5e6d7a8186|ca4f5b1f-0f8c-4f10-969c-464ccf207d9c
</code></pre>

<p>[desired output]</p>

<p>This is what the game info of the player's current game should look like. The query should take only the player's UUID and return the following if I the UUID was equal to 863fdf91-86fb-49a7-9232-bcb596e3a86f</p>

<pre><code>member_id                           | game_id       
------------------------------------|------------------------------------
863fdf91-86fb-49a7-9232-bcb596e3a86f|dd76c680-5853-40a6-b757-0457d1a7e95f
7af64cd7-72a2-410f-9b5c-620127fca0ac|dd76c680-5853-40a6-b757-0457d1a7e95f
</code></pre>