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

user_1272105 0
Viewed 94

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'?

join mysql sql
3 Answers

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
user_69148640

You can do it by joining to the userdata table a second time.

Note I've used modern INNER JOIN syntax to make it clearer what the join clauses apply to. I suggest that you should aim to use the modern syntax wherever possible to make your code clearer and cleaner.

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

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;

enter image description here

Demo

user_18632290
Related