How to select two different values from a table with different conditions in mysql?

Question

I have two tables:

userdata

guidUser  username  
-----------------
12        plr1        
13        plr2
14        plr3
15        plr4

games

id  guidUser1  guidUser2
-------------------------
1       12        13
2       15        14

I want to select names of players from the same userdata table based on their guid.

The result I'm trying to obtain is:

id  guidUser1   username  guidUser2  username2
--------------------------------------------
1       12        plr1        13        plr2
2       15        plr4        14        plr3

If the value was only one I could do

SELECT g.id, g.guidUser1, u.username, g.guidUser2 from games g, userdata u WHERE g.guidUser1=u.guidUser1 

But how I can obtain the second username 'username2'?

Answer 1

You can try below - using joining of multiple instance of userdata table

 SELECT g.id, g.guidUser1, u.username, g.guidUser2 u1.username as username2
    from 
    games g inner join userdata u on g.guidUser1=u.guidUser
    inner join userdata u1 on g.guidUser2=u1.guidUser

Answer 2

You may join twice. Each join to the userdata table brings in one username:

SELECT
    g.id,
    g.guidUser1,
    u1.username,
    g.guidUser2,
    u2.username AS username2
FROM games g
INNER JOIN userdata u1
    ON g.guidUser1 = u1.guidUser
INNER JOIN userdata u2
    ON g.guidUser2 = u2.guidUser;

Demo