Query 2 databases
(1) By oneeyeman on 2020-10-06 19:38:01 [link]
Hi, ALL, 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? Thank you.
(2) By Warren Young (wyoung) on 2020-10-06 19:45:18 in reply to 1 [link]
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 [`ATTACH DATABASE`](https://www.sqlite.org/lang_attach.html)?
(3) By oneeyeman on 2020-10-06 19:53:44 in reply to 2 [link]
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: 1. sqlite3 conn.db 2. attach att.db 3. SELECT.... Thank you.
(4) By Warren Young (wyoung) on 2020-10-06 20:06:35 in reply to 3 [link]
> 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 No: `ATTACH 'att.db' as 'att';` Then your SQL works, presuming that `att.db` contains `table2`, etc.
(5) By oneeyeman on 2020-10-06 21:58:13 in reply to 4 [link]
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. Thank you.
(6) By Keith Medcalf (kmedcalf) on 2020-10-06 22:08:49 in reply to 4
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> ```
(7) By Keith Medcalf (kmedcalf) on 2020-10-06 22:14:06 in reply to 6 [link]
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.
(8) By oneeyeman on 2020-10-06 22:44:06 in reply to 6 [link]
Keith, Thank you for the info. As I described in my message I simply stated the fact on how Warren put his statement in the answer to the OP. However I never knew that the original schema is called "main". Thank you.
(9) By oneeyeman on 2020-10-06 22:45:17 in reply to 7 [link]
And yes - I do know this. Otherwise one will not be able to safely open the "main" database. Thank you.