CODEWITHSUNDEEP
mysqlsql
mgabor
mgabor

Reputation: 953

MySQL: Get two rows as one

users table:

id  |   win  |   lose
0   |   1    |   2
1   |   2    |   8

games table:

id  |  user1  |  user2  |  data
0   |  0      |  1      |  'some text'

The DB is for a simple multiplayer game. I need a query to get user2, data, and win-lose data for both users. Is this possible in MySQL at all? Any help would be greatly appreciated.

Upvotes: 2

Views: 109

Answers (2)

The Nail
The Nail

Reputation: 8490

You can join the users table twice:

SELECT
    games.user2 AS user2_id
    games.data AS games_data
    user1.win AS user1_win,
    user1.lose AS user1_lose,
    user2.win AS user2_win,
    user2.lose AS user2_lose
FROM games
JOIN users AS user1 ON user1.id = games.user1
JOIN users AS user2 ON user2.id = games.user2
WHERE games.id = ...

Upvotes: 1

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79889

You have to JOIN the table users two times like this:

SELECT
  u1.win  AS win1,
  u1.lose AS lose1,
  u2.win  AS win2,
  u2.lose AS lose2,
  ...
FROM games g
INNER JOIN users u1 ON g.user1 = u1.id
INNER JOIN users u2 ON g.user2 = u2.id

Upvotes: 4

Related Questions