title: Combining records from multiple tables in SQL
body: <p>I currently have the below SQL query:</p>
  
  <pre><code>SELECT LastName
      ,FirstName
      ,BirthYear
      ,LocalExp
      ,TotalExp
      ,TotSalary
      ,TotFringe
      ,WorkLocationName
      ,SchoolName
  FROM DPISTAFF2008_2009
      ,DPISTAFF2009_2010
      ,DPISTAFF2010_2011
      ,DPISTAFF2011_2012
      ,DPISTAFF2012_2013
      ,DPISTAFF2013_2014
      ,DPISTAFF2014_2015
      ,DPISTAFF2015_2016
  WHERE DPISTAFF2015_2016.FirstName = 'George'
  </code></pre>
  
  <p>There are multiple tables, each with the same type of information for each year. I would like to retrieve all records from each table that match the WHERE information. Assuming George is unique, that would be 1 entry from each table so I can compare his information for each year.</p>
  
  <p>I am getting the "ambigious_column_error" even though I have added a table ID in my WHERE statement. Is there anything else I need to add to make this work?</p>
  
  <p><strong>UPDATED QUERY:</strong>
  I tried this to see if it would work. Here I have just 2 tables instead of all 6 so I can try to get it working before I do all that work (and likely I would use variables when implementing this so I can change the name easily). It did not. I get <code>near ";": syntax error</code></p>
  
  <pre><code>CREATE TEMP TABLE IF NOT EXISTS _Variables (Name TEXT PRIMARY KEY, Value TEXT);
  INSERT OR REPLACE INTO _Variables VALUES ('VarFirstName', 'John');
  INSERT OR REPLACE INTO _Variables VALUES ('VarLastName', 'Smith');
  
  SELECT LastName,FirstName,BirthYear,(strftime('%Y', date('now')) - BirthYear) AS Age,LocalExp,TotalExp,TotSalary,TotFringe,WorkLocationName,SchoolName
  FROM DPISTAFF2008_2009
  WHERE DPISTAFF2008_2009.FirstName = (SELECT Value FROM _Variables WHERE Name = 'VarFirstName')
      AND DPISTAFF2008_2009.LastName = (SELECT Value FROM _Variables WHERE Name = 'VarLastName')
  UNION ALL
  
  SELECT LastName,FirstName,BirthYear,(strftime('%Y', date('now')) - BirthYear) AS Age,LocalExp,TotalExp,TotSalary,TotFringe,WorkLocationName,SchoolName
  FROM DPISTAFF2009_2010
  WHERE DPISTAFF2009_2010.FirstName = (SELECT Value FROM _Variables WHERE Name = 'VarFirstName')
      AND DPISTAFF2009_2010.LastName = (SELECT Value FROM _Variables WHERE Name = 'VarLastName')
  UNION ALL
  
  ;DROP TABLE _Variables;
  </code></pre>
  
  <p>If I just do this once from one table with no UNION ALLs, the query successfully returns 1 record from that table.</p>