MySQL: select two column from the same table with the same name
<p>I'm creating an application to manage <code>game</code> and <code>team</code> data. Here is mySQL db structure:</p>

<pre><code>CREATE TABLE team (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE game (
    id INT NOT NULL AUTO_INCREMENT,
    id_team_A INT NOT NULL,
    id_team_B INT NOT NULL,
    PRIMARY KEY (id)
);

-- TEST VALUES: --

INSERT INTO team VALUES
    (1, "Barcelona"),
    (2, "Milan"),
    (3, "Real Madrid"),
    (4, "Inter");

INSERT INTO game VALUES
    (1, 1, 3),
    (2, 2, 4);
</code></pre>

<p>As you can see every <code>match</code> has 2 different <code>team</code> associated.</p>

<p>I'm trying (without success) to get in a single query <strong>match id, team A name, team B name</strong>.</p>

<p>First I tried a query like this:</p>

<pre><code>SELECT game.id, team.name, team.name
FROM team INNER JOIN game
ON game.id_team_A = team.id;
</code></pre>

<p>But my query don't care about <code>match.id_team_B</code> and duplicated <code>match.name</code> fields won't solve the problem.</p>

<p>Then I started using select query nested together:</p>

<pre><code>SELECT @g_id := game.id as id,
(SELECT team.name FROM team INNER JOIN game ON game.id_team_A = team.id WHERE game.id = @g_id) AS team_A,
(SELECT team.name FROM team INNER JOIN game ON game.id_team_B = team.id WHERE game.id = @g_id) AS team_B
FROM game;
</code></pre>

<p>And finally I got what I wanted:</p>

<pre><code>+-----+---------------+---------------+
| id  |    team_A     |    team_B     |
+-----+---------------+---------------+
|  1  |   Barcelona   |  Real Madrid  |
+-----+---------------+---------------+
|  2  |     Milan     |     Inter     |
+-----+---------------+---------------+
</code></pre>

<p><strong>Is there an easier/faster/cooler query to got the same result?</strong></p>

<p>NOTE: <a href="http://sqlfiddle.com/#!9/e477ae/12" rel="nofollow">SQL Fiddle</a></p>