CODEWITHSUNDEEP
sqlsqlite
InterLinked
InterLinked

Reputation: 1403

Create virtual column containing name of table?

I have a series of DPISTAFF tables from several years (i.e. DPISTAFF2010_2011). They each contain close to 200,000 records. I would like to be able to run a query that returns one record from each table for that individual so I can compare certain values across years. I am using a UNION JOIN to do this.

Currently I have the following query:

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;

The variable part is working fine. If I run just one SELECT statement without the second one and the UNION ALLs, then the query successfully returns 1 record, but currently I am getting near ";": syntax error: I don't know what my syntax error is but I think this is the right idea.

My primary issue would be differentiating between the records that are returned. What I would like to do is generate a column for viewing that would contain the name of the table that would record was returned from (at least the numbers, I only need the 2010-2011, not the DPISTAFF part). But I don't want to modify the table in any way. I already have a column called Age that is dynamically generated without modifying the table. Is it possible to get the name of the table in a similar manner?

Upvotes: 0

Views: 168

Answers (1)

Joe Farrell
Joe Farrell

Reputation: 3542

You're getting a syntax error when you try to run the full query because your SELECT statement is of the form:

select /* stuff */
union all
select /* more stuff */
union all;

You can't end a query with a UNION as you do here; the UNION operator appears between two queries whose result sets you wish to merge. So if you only have two tables from which to select, only one UNION ALL is necessary:

select /* stuff */
union all
select /* more stuff */;

If you have three tables, then you need two UNION ALLs:

select /* stuff */
union all
select /* more stuff */
union all
select /* even more stuff */;

And so on. As for adding the year into your result set, you can put a string literal right into your SELECT list and assign it an alias. So your overall query with two staff tables might look like this:

create temp table if not exists _Variables (Name text primary key, Value text);
insert or replace into _Variables values
    ('VarFirstName', 'John'), 
    ('VarLastName', 'Smith');

select '2008-2009' as Year, T1.FirstName, T1.LastName /* more fields */ 
    from DPISTAFF2008_2009 T1
    where T1.FirstName = (select Value from _Variables where Name = 'VarFirstName' limit 1)
        and T1.LastName = (select Value from _Variables where Name = 'VarLastName' limit 1)
union all
select '2009-2010' as Year, T2.FirstName, T2.LastName /*more fields */ 
    from DPISTAFF2009_2010 T2
    where T2.FirstName = (select Value from _Variables where Name = 'VarFirstName' limit 1)
        and T2.LastName = (select Value from _Variables where Name = 'VarLastName' limit 1);

drop table _Variables;

Output:

sqlite> .read samplequery.sql
2008-2009|John|Smith
2009-2010|John|Smith

Upvotes: 2

Related Questions