title: Query SQL Database for Home and Away team based on Team ID
body: <p>I'm working on setting up a Football (Soccer) betting database using data from Betfair. This is my first ever Database and I'm working in MS SQL Server 2012.
  I have set up 4 tables: Match, Team, Competition &amp; Data.
  My Match table contains columns: MatchID(PK), CompetitionID(FK), DateKickOff, TimeKickOff, TeamIDHome(FK), TeamIDAway(FK), ScoreHome, ScoreAway
  My Team table contains columns: TeamID(PK), TeamName</p>
  
  <p>I have been trying out some queries at this stage so that I can have a think about whether my tables are Normalised correctly. I wanted to run a query that would return 4 columns: Home Team Name, Home Score, Away Score, Away Team Name.</p>
  
  <p>If I run this I get the first 3 columns back:</p>
  
  <pre><code>SELECT TeamName TeamH, ScoreHome, ScoreAway
  FROM Match AS t
      INNER JOIN Team AS n
      ON t.TeamIDHome = n.TeamID
  </code></pre>
  
  <p>If I run this I get my fourth column:</p>
  
  <pre><code>SELECT TeamName TeamA
  FROM Match AS t
      INNER JOIN Team AS n
      ON t.TeamIDAway = n.TeamID
  </code></pre>
  
  <p>But I cannot Union the 2 functions as I get an error: All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.
  I believe that the error is caused because it tries to add the Away Team Names to the same column as the home team names. Can anybody suggest how I can query this correctly or let me know if I have a bad database design? Thanks</p>