Combining records from multiple tables in SQL

Question

I currently have the below SQL query:

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'

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.

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?

UPDATED QUERY: 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 near ";": syntax error

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;

If I just do this once from one table with no UNION ALLs, the query successfully returns 1 record from that table.

Answer 1

Because your table schema isn't that great (These should all be 1 table combined with a 'Year' column), you'll have to do a big union:

SELECT LastName
    ,FirstName
    ,BirthYear
    ,LocalExp
    ,TotalExp
    ,TotSalary
    ,TotFringe
    ,WorkLocationName
    ,SchoolName
FROM DPISTAFF2008   
WHERE FirstName = 'George'
UNION ALL
SELECT LastName
    ,FirstName
    ,BirthYear
    ,LocalExp
    ,TotalExp
    ,TotSalary
    ,TotFringe
    ,WorkLocationName
    ,SchoolName
FROM DPISTAFF2009   
WHERE FirstName = 'George'
UNION ALL
SELECT LastName
    ,FirstName
    ,BirthYear
    ,LocalExp
    ,TotalExp
    ,TotSalary
    ,TotFringe
    ,WorkLocationName
    ,SchoolName
FROM DPISTAFF2010   
WHERE FirstName = 'George'
UNION ALL
SELECT LastName
    ,FirstName
    ,BirthYear
    ,LocalExp
    ,TotalExp
    ,TotSalary
    ,TotFringe
    ,WorkLocationName
    ,SchoolName
FROM DPISTAFF2011   
WHERE FirstName = 'George'
UNION ALL
SELECT LastName
    ,FirstName
    ,BirthYear
    ,LocalExp
    ,TotalExp
    ,TotSalary
    ,TotFringe
    ,WorkLocationName
    ,SchoolName
FROM DPISTAFF2012   
WHERE FirstName = 'George'
UNION ALL
SELECT LastName
    ,FirstName
    ,BirthYear
    ,LocalExp
    ,TotalExp
    ,TotSalary
    ,TotFringe
    ,WorkLocationName
    ,SchoolName
FROM DPISTAFF2013   
WHERE FirstName = 'George'
UNION ALL
SELECT LastName
    ,FirstName
    ,BirthYear
    ,LocalExp
    ,TotalExp
    ,TotSalary
    ,TotFringe
    ,WorkLocationName
    ,SchoolName
FROM DPISTAFF2014   
WHERE FirstName = 'George'
UNION ALL
SELECT LastName
    ,FirstName
    ,BirthYear
    ,LocalExp
    ,TotalExp
    ,TotSalary
    ,TotFringe
    ,WorkLocationName
    ,SchoolName
FROM DPISTAFF2015   
WHERE FirstName = 'George'
UNION ALL
SELECT LastName
    ,FirstName
    ,BirthYear
    ,LocalExp
    ,TotalExp
    ,TotSalary
    ,TotFringe
    ,WorkLocationName
    ,SchoolName
FROM DPISTAFF2016   
WHERE FirstName = 'George'

---

Update to speak more about the schema.

A better schema that would speed up selection for this data, and drastically reduce the complexity of this sql and data is something like:

CREATE TABLE DPISTAFF
(
    recordyear int,
    LastName varchar(100),
    FirstName varchar(100),
    BirthYear int,
    LocalExp varchar(20),
    TotalExp  varchar(20),
    TotSalary Decimal (18,2),
    TotFringe Decimal(18,2)
    WorkLocationName varchar(100)
    SchoolName varchar(100)
);

You can then create an index on firstname to make selection against a particular person VERY fast.

CREATE INDEX dpistaff_firstname ON dpistaff (firstname);

Now your query is just:

SELECT  
    RecordYear
    ,LastName
    ,FirstName
    ,BirthYear
    ,LocalExp
    ,TotalExp
    ,TotSalary
    ,TotFringe
    ,WorkLocationName
    ,SchoolName
FROM DPISTAFF
WHERE Firstname = 'George';

If you find that you very often select on the year, like "give me everyone for 2008" then you can also add an index for recordyear. You may also (or alternatively) want an index on (recordYear, FirstName) if you often select for both of those at the same time like "give me Bill's data for 2016"

If your RDBMS supports partitioning, you may consider, instead of the index, a partition on recordyear. I'm guessing by your syntax though that you are on sqlLite so the indexes will have to suffice here. Plus your data is pretty small, so a partitioning scheme might be a little overkill.

The big take-away here is that if you find yourself having to often create a new table, or add columns just because we are in a new month/quarter/year your schema is probably not great. We aim to build the schema once and then use it for years. Only adding new objects (databases, tables, columns) when we add new objects. In this case a new table to store attributes of "School" like SchoolAddress, SchoolPhone. And then maybe later add a table to store ClassRooms and it's attributes.

Answer 2

All the DPISTAFF tables have same columns. I would recommend to create a procedure that takes a name as input.

Within procedure use following:

select LastName........ from DPISTAFF2008_2009 where name='George'
union
select LastName........ from DPISTAFF2009_2010 where name='George'

If you have to fire such query repeatedly and you have a huge databases then you need a more sophisticated solution.

Answer 3

Seem multiple table have same column name, you should use table name or table alias name before each column in select clause as

Tablename.columnname