mysql query table join

Question

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

ypercubeᵀᴹ · Accepted Answer · 2011-12-27 18:07:16Z

1

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)

edited Dec 27, 2011 at 18:07

ypercubeᵀᴹ

116k19 gold badges181 silver badges249 bronze badges

answered Dec 27, 2011 at 17:46

smp7d

5,0422 gold badges28 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

David · Accepted Answer · 2011-12-27 17:47:52Z

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.

Collectives™ on Stack Overflow

mysql query table join

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related