title: Query 2 tables correctly in SQL Server
body: <p>I have 2 tables in my SQL database named 'Team' and 'TeamAlias'. The first tables holds the proper full names of some football teams, and the other hold abbreviated names. Both tables contain the TeamID that identifies the teams. When I import data to my database the data could contain the proper name or an alias. I need to query both tables to get the ID of both the home and away teams. The following code gives me the correct result when both team names are in the Team table or both are in the TeamAlias table:</p>
  
  <pre><code>INSERT INTO temp2 (TeamIDHome, TeamIDAway)
  select HomeTeam.TeamID as TeamIDHome, AwayTeam.TeamID as TeamIDAway
  from temp1 as U
  join Team as HomeTeam on U.TeamNameHome = HomeTeam.TeamName
  join Team as AwayTeam on U.TeamNameAway = AwayTeam.TeamName
  UNION ALL
  select HomeTeam.TeamID as TeamIDHome, AwayTeam.TeamID as TeamIDAway
  from temp1 as U
  join TeamAlias as HomeTeam on U.TeamNameHome = HomeTeam.TeamName
  join TeamAlias as AwayTeam on U.TeamNameAway = AwayTeam.TeamName
  </code></pre>
  
  <p>My problem occurs when one team in is the Team table and the other is in the TeamAlias table. In that case no results are returned. I would appreciate some help to configure this query correctly.</p>
  
  <p>Here are extracts from the tables that I am using:</p>
  
  <p>temp1:</p>
  
  <pre><code>CompetitionID   DateKickOff             TimeKickOff         TeamIDHome  TeamIDAway  TeamNameHome    TeamNameAway    ScoreHome   ScoreAway
  17              2013-08-10 00:00:00.000 13:31:00.0000000    NULL        NULL        Augsburg        Dortmund        0           4
  </code></pre>
  
  <p>temp2:</p>
  
  <pre><code>    TeamIDHome    TeamIDAway
      NULL          NULL
  </code></pre>
  
  <p>Team:</p>
  
  <pre><code>TeamID  TeamName                TeamLocation
  139     Augsburg                Germany
  140     Bayern Munich           Germany
  141     Borussia Dortmund       Germany
  142     Eintracht Braunschweig  Germany
  143     Eintracht Frankfurt     Germany
  </code></pre>
  
  <p>TeamAlias:</p>
  
  <pre><code> TeamID TeamName
   161    Duisburg
   162    FC Koln
   164    G Furth
   165    H Rostock
   141    Dortmund 
  </code></pre>
  
  <p>The answer that I need should populate the NULL fields in table temp2 with values 139 and 141.</p>
  
  <p>EDIT:
  Final Solution was:</p>
  
  <pre><code>;WITH TeamInfo ( ID, Name, Alias ) 
  AS (
      SELECT t.TeamID, t.TeamName, ta.TeamName
      FROM Team t
          FULL OUTER JOIN TeamAlias ta ON t.TeamID = ta.TeamID
  )
  INSERT INTO temp2 (TeamIDHome, TeamIDAway)
  SELECT home.ID AS TeamIDHome, away.ID AS TeamIDAway
      FROM temp1 g
          INNER JOIN TeamInfo home ON g.TeamNameHome IN ( home.Name, home.Alias )
          INNER JOIN TeamInfo away ON g.TeamNameAway IN ( away.Name, away.Alias )
  </code></pre>