SQLite syntax help

Hi. I’ve been racking my brain on this one for a while. I need a little help with a syntax statement.

There are two database files, we’ll call them db1 and db2. db2 contains a table (table2 for simplicity) with some referenceID numbers that will also be contained as a referenceID number in a table (table1) in db1. I want to pull only the referenceID numbers that are not in table2 of db2

For example, say table1 in db1 contains referenceID numbers 1, 2, 3, 4, 5, 6, 7, 8, 9
And table2 in db2 contains referenceID numbers 2, 4, 6, 8
The data I want pulled is 1, 3, 5, 7, 9

Can someone help with how to write the SQL statement here? Please note, the numbers above are just used as a simplifed example. The actual referenceID numbers will differ

Since you have two distinct databases, you’ll need first to query one, then the other. Take a look at IN() and NOT IN() respectively.

Thanks Alex. Are you suggesting

SELECT referenceID FROM table1 NOT IT (SELECT referenceID FROM table2)

I just tried that, but it didn’t work. I’m not familiar with IN and NOT IN yet

Assuming that you connected the second database, you need to specify the database identifier in your sub-select. And you need to add a where clause.

select referenceID from table1 where referenceID not in (select referenceID from db2.table2)

Got it! I wasn’t attaching them prior. Now am and it’s good. Thank you Alex and Tim!