MySQL - Select multiple rows from one table whose IDs are stored in another table

Question

I'm confused using a JOIN - Statement to select my data from my MySQL Database. Here is how the tables look like:

Table "Users"
+----+-------+----------+
| ID | Name  | Password |
+----+-------+----------+
| 1  | Mike  | test     |
| 2  | Tony  | test1    |
| 3  | Frank | test2    |
+----+-------+----------+

Table "Games"
+----+-----------+-----------+---------+---------+
| ID | Player1ID | Player2ID | ScoreP1 | ScoreP2 |
+----+-----------+-----------+---------+---------+
| 1  |         1 |         2 |       5 |       2 |
| 2  |         3 |         1 |       2 |       1 |
+----+-----------+-----------+---------+---------+

I would like to SELECT * FROM GAMES WHERE Player1ID=1 or Player2ID=1 plus the names of the users and not just their IDs. Can someone help me with that?

juergen d · Accepted Answer · 2014-10-08 14:56:21Z

3

Join the users table twice with different alias names for them to distinguish them

select g.*, u1.name as player1, u2.name as player2
from games g
join users u1 on u1.id = g.player1id
join users u2 on u2.id = g.player2id

answered Oct 8, 2014 at 14:56

juergen d

205k40 gold badges303 silver badges377 bronze badges

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

2 Comments

Mike_NotGuilty Over a year ago

I will try that... thanks! Can you check my EDIT. What if want to SELECT...WHERE ? thanks

juergen d Over a year ago

Just add the where clause to the end of this query

Collectives™ on Stack Overflow

MySQL - Select multiple rows from one table whose IDs are stored in another table

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related