Query 2 databases
Lets say I opened a connection to a database and want to attach a second database.
Lets call connected DB a conn.db and attached one - att.db.
Will I be able to do an inter-DB query?
If yes - what would be the syntax?
[code] SELECT conn.table1.field1, att.table2.field2 FROM conn.table1, att.table2 WHERE conn.table1.id = att.table2.id; [/code]
Or something different?
Based on the wording of your question and the SQL you've included as an example, I think the answer is, "Yes." :)
Are you looking for something other than
Hi, Warren, Is there any other command by which I can "attach database"?
Now is the syntax I put in is correct?
Consider this scenario:
- sqlite3 conn.db
- attach att.db
Is there any other command by which I can "attach database"?
Why do you want one? What's wrong with the one I pointed you to?
ATTACH 'att.db' as 'att';
Then your SQL works, presuming that
Hi, Warren, From you first reply:
[quote] Are you looking for something other than ATTACH DATABASE? [/quote]
from which I presume there is an other way to attach the database than issue "ATTACH" statement.
No. It does not and will not.
The database that is connected to by the sqlite3_open* series of API calls (or the filename argument to the sqlite3 CLI command, which merely passes the filename given to the sqlite3_open* API) has the schema identifier "main".
You do not have a choice to "like" or "not like" this fact, it simply is a fact that you cannot change. Get used to it.
The identifier of an additional database file attached to the connection with the ATTACH command can be given any valid identifier you please as long as that identifier IS NOT "main" or "temp" as these are already in use.
>sqlite conn.db SQLite version 3.34.0 2020-10-05 19:05:20 Enter ".help" for usage hints. sqlite> attach 'att.db' as att; sqlite> .databases main: D:\Source\bld\conn.db r/w att: D:\Source\bld\att.db r/w sqlite> create table main.table1(id,field1); sqlite> create table att.table2(id,field2); sqlite> SELECT main.table1.field1, att.table2.field2 FROM main.table1, att.table2 WHERE main.table1.id = att.table2.id; sqlite> .tables att.table2 table1 sqlite>
Notice that each database attached to a connection must be cohesive.
That means that you CANNOT have cross schema triggers, indexes, or foreign key constraints. Other than special dispensation for triggers created in the TEMP schema, this is also simply a fact that, whether you may like or dislike it, is not subject to dispute.
And yes - I do know this. Otherwise one will not be able to safely open the "main" database.