Databases
StackOverflow2013
Postsdbo
POSQL: How to make multiple joins to the same column of a table without overriding results?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL: How to make multiple joins to the same column of a table without overriding results?
    text
    copied!<p>I have a table of basketball matches and a table of basketball teams like this:</p> <pre><code>MATCHES: ID | HOME_TEAM_ID | AWAY_TEAM_ID | SCORE_HOME | SCORE_AWAY ---------------------------------------------------------------- 1 | 20 | 21 | 80 | 110 2 | 12 | 10 | 96 | 90 TEAMS: ID | NAME ------------------------- 20 | BULLS 21 | KNICKS </code></pre> <p>Given a match ID, I would like to retrieve both the score and the team names. How do I make a JOIN that retrieves both team names from the teams table?</p> <p>I have tried:</p> <pre><code>SELECT * FROM matches AS m JOIN teams AS t1 ON t.id = m.home_team_id JOIN teams AS t2 ON ti.id = m.away_team_id WHERE m.id = 1 </code></pre> <p>...but here the result from the second JOIN statement seems to override the one from the first, so I only get one name:</p> <pre><code>[id] =&gt; 1 [score_home] =&gt; 80 [score_away] =&gt; 110 [name] =&gt; KNICKS </code></pre> <p>I have also tried:</p> <pre><code>SELECT * FROM matches AS m JOIN teams AS t ON (t.id = m.home_team_id OR t.id = m.away_team_id) WHERE m.id = 1 </code></pre> <p>...which returns two results :</p> <pre><code>[id] =&gt; 1 [score_home] =&gt; 80 [score_away] =&gt; 110 [name] =&gt; BULLS </code></pre> <p>and</p> <pre><code>[id] =&gt; 1 [score_home] =&gt; 80 [score_away] =&gt; 110 [name] =&gt; KNICKS </code></pre> <p>I would like to make a query that returns something like this</p> <pre><code>[id] =&gt; 1 [score_home] =&gt; 80 [score_away] =&gt; 110 [name_home_team] =&gt; BULLS [name_home_team] =&gt; KNICKS </code></pre> <p>Is that possible?</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload