title: sqlite left join syntax with create table as
body: <p>I have two large tables in sqlite3 and need to do a left join and create a new table (myTable). I also need to match two columns (different names) from each of the tables and then rename them for the third table (myTable). Below is my current code, but I keep receiving an error at the end: ambiguous column name faa.city</p>
  
  <p>Table 1: airplane</p>
  
  <p>Table 2: faa</p>
  
  <p>First, I'm matching airplane.code with faa.iata and then renaming it to oCity (myTable.oCity). Second, I'm matching airplane.dCode with faa.city and renaming it to dCity (myTable.dCity).</p>
  
  <pre><code>CREATE TABLE myTable AS
  SELECT airplane.code AS oCity, faa.city AS dCity, airplane.month, airplane.dayOfMonth, airplane.uniqueCode, airplane.fNumber, airplane.scheduledTime
  FROM airplane, faa
  LEFT JOIN faa
  WHERE airplane.code = faa.iata
  AND airplane.dCode = faa.city;
  </code></pre>