title: Query in SQL Server, trying to merge two queries -- EDIT: Adding yet another query
body: <p>I have this two queries:</p>
  
  <pre><code>SELECT localteam = teams.team_name from teams, games WHERE games.loc_team_number = teams.team_number
  
  SELECT visitingteam = teams.team_name from teams, games WHERE games.vis_team_number = teams.team_number
  </code></pre>
  
  <p>And I'm trying to merge them but I just can't seem to get it right.
  I'm trying this:</p>
  
  <pre><code>SELECT games.week, locteam, visteam, games.loc_goals, games.vis_goals
  FROM games
  JOIN (select locteam = teams.team_name from teams) AS locteam ON games.loc_team_number = teams.team_number
  JOIN (select visteam = teams.team_name from teams) AS visteam ON games.vis_team_number = teams.team_number
  </code></pre>
  
  <p>But I get this error:</p>
  
  <p>Msg 4104, Level 16, State 1, Line 118
  The multi-part identifier "teams.team_number" could not be bound.
  Msg 4104, Level 16, State 1, Line 119
  The multi-part identifier "teams.team_number" could not be bound.</p>
  
  <p>Each query works fine separately.</p>
  
  <p>EDIT:</p>
  
  <p>First answer got it right, thanks to anyone who replied</p>
  
  <p>EDIT 2:</p>
  
  <p>This answer did it:</p>
  
  <pre><code>SELECT games.week, locteam, visteam, games.loc_goals, games.vis_goals
  FROM games
  JOIN (select locteam = teams.team_name, team_number from teams) AS locteam ON games.loc_team_number = locteam.team_number
  JOIN (select visteam = teams.team_name, team_number from teams) AS visteam ON games.vis_team_number = visteam.team_number
  </code></pre>
  
  <p>But if I wanted to add another field for the name of the team who won the game, would it be like this?: </p>
  
  <pre><code>SELECT games.week, locteam, visteam, games.loc_goals, games.vis_goals, winner
  FROM games
  JOIN (select locteam = teams.team_name, team_number from teams) AS locteam ON games.loc_team_number = locteam.team_number
  JOIN (select visteam = teams.team_name, team_number from teams) AS visteam ON games.vis_team_number = visteam.team_number
  JOIN (select winner = teams.team_name, team_number from teams) AS winner ON 
  </code></pre>
  
  <p>I can't think of any condition that would work</p>