[SOLVED] SQLite - Get all results from a table, but mark those who exist in another query

[Axeia]

Okay, I got the following table

CREATE TABLE IF NOT EXISTS Unchecked
(
    date      DATE,
    filename  VARCHAR(255) PRIMARY KEY,
    download  VARCHAR(255)
);

And I got a huge query, which I'll shorten for convenience

SELECT date, filename, download FROM UnChecked WHERE filename like "[imuR88-KSN]%";

 

This all works nicely, but what I want is everything from the UnChecked table, with the result from the query above having an extra field set to true, or null.. something I can later on use to determine whether they matched the like or not.

I tried creating a temporary table, but it doesn't seem to let me... and I'm quite sure this is somehow possible with a self join, maybe I'll figure it out after a good night of sleep.

 

 

This is what I tried:

CREATE TEMPORARY TABLE workaround( 
    date          DATE,
    filename      VARCHAR(255) PRIMARY KEY, 
    download      VARCHAR(255),
    interesting   BOOL DEFAULT FALSE
);
SELECT date, filename, download INTO workaround FROM UnChecked;
SELECT * FROM workaround;

But that last select comes up blank for some reason. (Was planning on doing a second query to set the 'interesting' boolean to true)

 

EDIT

We're talking about SQLite3 using the PDO driver btw.

[Axeia]

Okay figured out one way... although it took me ages.

CREATE TEMPORARY TABLE workaround( date DATE, filename VARCHAR(255) PRIMARY KEY, download VARCHAR(255), interesting BOOL DEFAULT 0 );
INSERT INTO workaround( date, filename, download ) SELECT * FROM UnChecked );
UPDATE workaround SET interesting=1 WHERE filename IN (
    SELECT filename FROM UnChecked WHERE filename like "[imuR88-KSN]%"
);
SELECT * FROM workaround;

 

For some reason using TRUE and FALSE instead of 0/1 gives a nice error... it thinks the TRUE is a column name..

DBO driver didn't show it.. so that took a while to figure out.