CODEWITHSUNDEEP
mysqljoin
user1118042
user1118042

Reputation: 163

mysql query table join

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

Answers (2)

smp7d
smp7d

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

David
David

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 JOINs instead so as to better handle null values, etc.

Upvotes: 1

Related Questions