Reputation: 163
I have two tables, GAMES and USERS set up with the following fields:
tblGAMES-->
GAME_ID (int) primary key,
P1_ID (int),
P2_ID (int),
P3_ID (int),
P4_ID (int)
tblUSERS-->
USER_ID (int) primary key,
FIRST_NAME (text),
LAST_NAME (text)
I am querying my database to print out the details of every game row. Example:
GAME_ID:1, P1:1, P2:2, P3:3, P4:4
but instead of printing out the id which is stored in tblGAMES I would like to print out the first name of each player as it appears in the corresponding row in tblUSERS. Is this doable using a single query as opposed to multiple queries?
end result-->GAME_ID:1, P1:David, P2:Paul, P3:John, P4:Bobby
Upvotes: 0
Views: 97
Reputation: 5032
You'll need to join the users table 4 times...
select g.GAME_ID,
u1.FIRST_NAME as ulname,
u2.FIRST_NAME as u2name,
u3.FIRST_NAME as u3name,
u4.FIRST_NAME as u4name
from tblGAMES as g
left join tblUSERS as u1 on g.P1_ID =u1.USER_ID
left join tblUSERS as u2 on g.P2_ID =u2.USER_ID
left join tblUSERS as u3 on g.P3_ID =u3.USER_ID
left join tblUSERS as u4 on g.P4_ID =u4.USER_ID
(untested)
Upvotes: 1
Reputation: 219127
In the query you're going to want to reference tblUSERS
once per JOIN
. Something like this:
SELECT
tblGAMES.GAME_ID,
tblUSER1.FIRST_NAME AS P1,
tblUSER2.FIRST_NAME AS P2,
tblUSER3.FIRST_NAME AS P3,
tblUSER4.FIRST_NAME AS P4
FROM tblGAMES
INNER JOIN tblUSERS AS tblUSER1 ON tblGAMES.P1_ID = tblUSER1.USER_ID
INNER JOIN tblUSERS AS tblUSER2 ON tblGAMES.P2_ID = tblUSER2.USER_ID
INNER JOIN tblUSERS AS tblUSER3 ON tblGAMES.P3_ID = tblUSER3.USER_ID
INNER JOIN tblUSERS AS tblUSER4 ON tblGAMES.P4_ID = tblUSER4.USER_ID
Note that my syntax may need a little tweaking, I haven't written enough MySQL recently to free-hand it 100% reliably. But you get the idea. Each join gets added to the overall product independently.
You may also change the JOIN
types depending on how required each player is. For example, if some games are 2-player then you'd want these to be LEFT OUTER JOIN
s instead so as to better handle null
values, etc.
Upvotes: 1