SQLITE, Create a temp table then select from it

Question

just wondering how i can create a temp table and then select from it further down the script.

Example.

CREATE TEMPORARY TABLE TEMP_TABLE1 AS 
Select 
L.ID,
SUM(L.cost)/2 as Costs,
from Table1 L
JOIN Table2 C on L.ID = C.ID
Where C.name  = 'mike'
Group by L.ID
Select 
Count(L.ID)
from Table1 L
JOIN TEMP_TABLE1 TT1 on L.ID = TT1.ID;
Where L.ID not in (TT1) 
And Sum(L.Cost) > TT1.Costs

Ideally I want to have a temp table then use it later in the script to reference from.

Any help would be great!

Solution

You simply refer to the table as temp.<table> or <table> the latter only if it is a unique table name.

As per :-

If a schema-name is specified, it must be either "main", "temp", or the name of an attached database. In this case the new table is created in the named database. If the "TEMP" or "TEMPORARY" keyword occurs between the "CREATE" and "TABLE" then the new table is created in the temp database. It is an error to specify both a schema-name and the TEMP or TEMPORARY keyword, unless the schema-name is "temp". If no schema name is specified and the TEMP keyword is not present then the table is created in the main database.

SQL As Understood By SQLite - CREATE TABLE

The following example creates 3 tables :-

1. table1 with 3 columns as a permanent table.
2. table1 a temporary copy of the permanent table1.
3. temp_table another temporary copy of the permanent table1.

:-

DROP TABLE IF EXISTS temp.table1;
DROP TABLE IF EXISTS table1;
DROP TABLE IF EXISTS temp_table;
CREATE TABLE table1 (columnA INTEGER,columnB INTEGER, columnC INTEGER);

1. When creating the permanent table 1 it is loaded with 4 rows

:-

INSERT INTO table1 (columnA,columnB,columnC) VALUES 
 (1,5,20),
 (2,7,21),
 (3,8,80),
 (4,3,63);
CREATE TEMP TABLE table1 AS select * from table1;;
CREATE TEMPORARY TABLE temp_table AS SELECT * FROM table1;

1. both temp tables are then used to in a union all to basically duplicate the rows, but with an indicator of the source table as a new column from_table
   1. Not that two forms of referring to the temp tables are used. temp. and just the table name.
   2. The latter only usable if the temporary table is a unique table name.

:-

SELECT 'temp_table' AS from_table,* FROM temp_table
UNION ALL 
SELECT 'temp.table1' as from_table,* FROM temp.table1;

The result being :-

---

Re addition of example :-

CREATE TEMPORARY TABLE TEMP_TABLE1 AS 
Select 
L.ID,
SUM(L.cost)/2 as Costs,
from Table1 L
JOIN Table2 C on L.ID = C.ID
Where C.name  = 'mike'
Group by L.ID
Select 
Count(L.ID)
from Table1 L
JOIN TEMP_TABLE1 TT1 on L.ID = TT1.ID;
Where L.ID not in (TT1) 
And Sum(L.Cost) > TT1.Costs

There are a few issues with this example bar the misuse of the aggregate (commented out) the following works.

• Note for my convenience I've added an _ to the table names.

:-

DROP TABLE IF EXISTS Table_1;
DROP TABLE IF EXISTS Table_2;
DROP TABLE If EXISTS temp.temp_table1;
CREATE TABLE Table_1 (ID INTEGER PRIMARY KEY, cost REAL);
CREATE TABLE Table_2 (ID INTEGER PRIMARY KEY, name TEXT);
INSERT INTO Table_1 (cost) VALUES (100.45),(56.78),(99.99);
INSERT INTO Table_2 (name) VALUES ('mike'),('mike'),('fred');
CREATE TEMP TABLE temp_table1 AS
SELECT L.ID, 
    sum(L.cost)/2 as Costs
FROM Table_1 L
    JOIN Table_2 C ON L.ID = C.ID
WHERE C.name = 'mike'
GROUP BY L.ID;
SELECT 
    count(L.ID)
FROM Table_1 L
   JOIN temp_table1 TT1 ON  L.ID = TT1.[L.ID]
WHERE 
    L.ID NOT IN (TT1.[L.ID])
    -- AND Sum(L.cost) > TT1.costs --<<<< misuse of aggregate

The issues are based upon the column name being L.ID so this has to be enclosed (rules here SQL As Understood By SQLite - SQLite Keywords apply) [ and ] have been used above.

• of course you could circumvent the need for enclosure by naming the column using AS e..g SELECT L.ID AS lid, --<<<< AS lid ADDED SUM(L.cost)/2 as Costs, ,.......

Adding the following may be suitable for getting around the misuse of aggregate :-

GROUP BY L.ID
HAVING sum(L.cost) > TT1.costs

---

Adding the following to the end of the script :-

 SELECT 
    count(L.ID), *
FROM Table_1 L
   JOIN temp_table1 TT1 ON  L.ID = TT1.[L.ID];

results in :-