SQLite - How do you join tables from different databases?
If ATTACH is activated in your build of Sqlite (it should be in most builds), you can attach another database file to the current connection using the ATTACH keyword. The limit on the number of db's that can be attached is a compile time setting(SQLITE_MAX_ATTACHED), currently defaults to 10, but this too may vary by the build you have. The global limit is 125.
attach 'database1.db' as db1;
attach 'database2.db' as db2;
You can see all connected databases with keyword
.databases
Then you should be able to do the following.
select
*
from
db1.SomeTable a
inner join
db2.SomeTable b on b.SomeColumn = a.SomeColumn;
Note that "[t]he database names main and temp are reserved for the primary database and database to hold temporary tables and other temporary data objects. Both of these database names exist for every database connection and should not be used for attachment".
SQLITE: Merge tables from different databases with Primary Key Constraint
First attach the 3 databases:
ATTACH "all\tab_X.db" AS db1;
ATTACH "all\tab_Y.db" AS db2;
ATTACH "all\tab_Z.db" AS db3;
Create the new table:
CREATE TABLE db3.PAYMENT(Pmt_ID TEXT PRIMARY KEY, P_CUSTOMER_NAME TEXT, P_CASH_AMOUNT REAL);
Finally insert the rows:
INSERT INTO db3.PAYMENT(Pmt_ID, P_CUSTOMER_NAME, P_CASH_AMOUNT)
SELECT 'X' || Pmt_ID, P_CUSTOMER_NAME, P_CASH_AMOUNT FROM db1.PAYMENT
UNION ALL
SELECT 'Y' || Pmt_ID, P_CUSTOMER_NAME, P_CASH_AMOUNT FROM db2.PAYMENT
SQLite - Joining 3 different databases at a single one
I believe that you have to believe what the message says. The following demonstrates that what you are doing does work:-
sqlite> .open 'C.db'
sqlite> ATTACH 'A.db' AS db1;
sqlite> ATTACH 'B.db' AS db2;
sqlite> PRAGMA database_list;
0|main|C:\Users\Mike\C.db
2|db1|C:\Users\Mike\A.db
3|db2|C:\Users\Mike\B.db
sqlite> CREATE TABLE IF NOT EXISTS main.C (ColumnInCommon);
sqlite> CREATE TABLE IF NOT EXISTS db1.A (ColumnInCommon);
sqlite> CREATE TABLE IF NOT EXISTS db2.B (ColumnInCommon);
sqlite> SELECT * FROM db1.A INNER JOIN db2.B ON B.ColumnInCommon = A.ColumnInCommon;
sqlite>
i.e. no error.
I can force an error using :-
sqlite> SELECT * FROM dbx.A INNER JOIN db2.B ON B.ColumnInCommon = A.ColumnInCommon;
Error: no such table: dbx.A
sqlite>
But this shouldn't apply as your ATTACH appears to have worked (pragma database_list; will confirm as used above). As such it is probably that the table itself does not exist. I'd suggest trying :-
SELECT * FROM db1.sqlite_master;
This would then show the tables in the A(db1) database e.g. :-
sqlite> SELECT * FROM db1.sqlite_master;
table|A|A|2|CREATE TABLE A (ColumnInCommon)
sqlite>
Merging two tables in sqlite from different database
Okey first you have to attach the databases, to your current connection.
SQLite give you this by using ATTACH.
The ATTACH DATABASE statement adds another database file to the current database connection.
ATTACH LINK
Run this:
attach database DatabaseA.db as DbA;
attach database DatabaseB.db as DbB;
Now you can reference the databases as you do with tables...
select
*
from
DbA.Table1 A
inner join
DbB.Table2 B on B.Emp_Id = A.Emp_Id;
There is a limit to the number of databases that can be simultaneously attached to a single database connection.
Check your settings if something goes wrong, the flag is:
#define SQLITE_LIMIT_ATTACHED 7
// SQLITE_LIMIT_ATTACHED - The maximum number of attached databases.
How to join two tables from two different databases in FMDB in a iOS app
Sqlite3 allows it to attach foreign database to the current connection.
Imagine, you have two databases, each one holds an own connection via FMDatabase (for multithreading purposes you should use FMDatabaseQueue instead).
Now, attach the other database to the connection of the first database. Then you can join from database 1 to a table in database 2. Important: all this happens via sqlite per sql statement and has nothing to do with FMDB.
I'll provide example-code at github:
https://github.com/itinance/MultiDatabaseJoin
For simplicity i placed the database-files under /tmp-directory. With iOS-Simulator this is working well, i haven't tested this code in a really device, where you would place the database in the document folder or something similar (but thats not the point yet).
The important thing here is the following query in sqlite3:
[db1 executeStatements:@"ATTACH DATABASE '/tmp/tmp2.db' AS second_db"];
At the database connection db1 we attach the database-file from the second database.
After this attaching you can join in Database-Connection 1 to a table in database connection 2 in a query like this one:
SELECT a.id, a.name AS aname, b.name AS bname FROM a INNER JOIN second_db.b ON b.a_id = a.id
Here is the whole code i tried:
FMDatabase *db1 = [FMDatabase databaseWithPath:@"/tmp/tmp1.db"];
FMDatabase *db2 = [FMDatabase databaseWithPath:@"/tmp/tmp2.db"];
[db1 open];
[db2 open];
[db1 executeStatements:@"CREATE TABLE a (id INTEGER, name TEXT)"];
[db1 executeStatements:@"INSERT INTO a (id, name) VALUES (1, 'foo'), (2, 'bar')"];
[db2 executeStatements:@"CREATE TABLE b (id INTEGER, a_id INTEGER, name TEXT)"];
[db2 executeStatements:@"INSERT INTO b (id, a_id, name) VALUES (1, 1, 'b_foo'), (2, 2, 'b_bar')"];
bool success = [db1 executeStatements:@"ATTACH DATABASE '/tmp/tmp2.db' AS second_db"];
if(!success) {
NSLog(@"%@", db1.lastErrorMessage);
return YES;
}
FMResultSet* rs = [db1 executeQuery:@"SELECT a.id, a.name AS aname, b.name AS bname FROM a INNER JOIN second_db.b ON b.a_id = a.id"];
while( [rs next]) {
NSLog(@"%@, %@", [rs stringForColumn:@"aname"], [rs stringForColumn:@"bname"]);
}
[rs close];
RoomDB - Can you JOIN tables from different databases
Does the fact I want to keep my db's seperate indicate a "smell"?
Yes, especially with Room. It introduces complexities and inefficiencies.
Is it possible to write a query where I can get the join model FooWithBar if the Foo table resides in a different database than the Bar table?
For your simple example Yes but as can be seen from the example not with an actual SQL JOIN (without attaching), that is you get the Foo object and mimic the JOIN by then getting the appropriate Bar (or Bars).
If you tried to mix Entities from different Databases then you will encounter problems e.g.
even though there are no issues adding (whilst editing) a Dao such as :-
@Query("SELECT * FROM foo JOIN bar ON foo.barId = bar.id")
fun getAllFooWithBar(): List
Which appears fine as per (screen shot from Android Studio) :-
When you compile you will get errors such as :-
E:\AndroidStudioApps\SO67981906KotlinRoomDate\app\build\tmp\kapt3\stubs\debug\a\a\so67981906kotlinroomdate\FooDao.java:22: error: There is a problem with the query: [SQLITE_ERROR] SQL error or missing database (no such table: bar)
public abstract void getAllFooWithBar();
^E:\AndroidStudioApps\SO67981906KotlinRoomDate\app\build\tmp\kapt3\stubs\debug\a\a\so67981906kotlinroomdate\FooDao.java:22: error: Not sure how to convert a Cursor to this method's return type (void).
public abstract void getAllFooWithBar();
To get anything that requires tables from both databases in a single query will be outside of the scope of Room as each database only knows about it's own tables.
However, if you ATTACH one database to another you then will have both database in one BUT Room does not understand it. So you basically have to revert to using a SupportSQLiteDatabase (similar to using native Android SQlite (but with some limitations)).
Example (very simple)
Foo Entity
@Entity
data class Foo(
@PrimaryKey
val fooId: Long?,
val barId: Long,
val fooText: String
)
- Basically the same
FooDao
@Dao
interface FooDao {
@Insert
fun insert(foo: Foo): Long
@Query("SELECT * FROM foo")
fun getAllFoos(): List
@Query("SELECT * FROM foo WHERE fooId=:fooId")
fun getFooById(fooId: Long): Foo
/* !!!!NO GO!!!!
@Query("SELECT * FROM foo JOIN bar ON foo.barId = bar.id")
fun getAllFooWithBar(): List
*/
}
- Some simple Dao's
FooDatabase
@Database(
entities = [Foo::class],
version = 1,
exportSchema = false
)
abstract class FooDatabase : RoomDatabase() {
abstract fun fooDao() : FooDao
fun attachBar(context: Context): Boolean {
var rv: Boolean = false
if (instance != null) {
val dbs = this.openHelper?.writableDatabase
val barpath = context.getDatabasePath(BarDatabase.DBNAME)
if (dbs != null) {
dbs.execSQL("ATTACH DATABASE '$barpath' AS $BAR_SCHEMA_NAME")
rv = true
}
}
return rv
}
fun closeInstance() {
if(instance == null) return
if (this.isOpen()) {
this.close()
}
instance = null
}
companion object {
@Volatile
private var instance: FooDatabase? = null
fun getInstanceWithForceOption(context: Context, forceReopen: Boolean = false): FooDatabase {
if (forceReopen) instance?.closeInstance()
if (instance == null) {
instance = Room.databaseBuilder(context,FooDatabase::class.java, DBNAME)
.allowMainThreadQueries()
.addCallback(FOO_CALLBACK)
.build()
}
return instance as FooDatabase
}
fun getInstance(context: Context): FooDatabase {
return getInstanceWithForceOption(context, false)
}
val FOO_CALLBACK = object: RoomDatabase.Callback() {
override fun onOpen(db: SupportSQLiteDatabase) {
super.onOpen(db)
}
override fun onCreate(db: SupportSQLiteDatabase) {
super.onCreate(db)
}
}
const val DBNAME: String = "foo.db"
const val BAR_SCHEMA_NAME = "bar_schema"
}
}
- CallBacks not used but ATTACH could be done in onOpen if ALWAYS access via attach
Bar Entity
@Entity
data class Bar(
@PrimaryKey
val id: Long?,
val barText: String
)
- Basically the same
BarDao
@Dao
interface BarDao {
@Insert
fun insert(bar: Bar): Long
@Query("SELECT * FROM bar")
fun getAllBars(): List
@Query("SELECT * FROM Bar WHERE id=:id")
fun getBarById(id: Long): Bar
}
BarDatabase
@Database(
entities = [Bar::class],
version = 1,
exportSchema = false
)
abstract class BarDatabase : RoomDatabase() {
abstract fun barDao() : BarDao
fun closeInstance() {
if (this.isOpen()) {
this.close()
}
instance = null
}
companion object {
@Volatile
private var instance: BarDatabase? = null
fun getInstanceWithForceOption(context: Context, forceReopen: Boolean = false): BarDatabase {
if (forceReopen) instance?.closeInstance()
if (instance == null) {
instance = Room.databaseBuilder(context,BarDatabase::class.java, DBNAME)
.allowMainThreadQueries()
.addCallback(BAR_CALLBACK)
.build()
}
return instance as BarDatabase
}
fun getInstance(context: Context): BarDatabase {
return getInstanceWithForceOption(context, false)
}
val BAR_CALLBACK = object: RoomDatabase.Callback() {
override fun onOpen(db: SupportSQLiteDatabase) {
super.onOpen(db)
}
override fun onCreate(db: SupportSQLiteDatabase) {
super.onCreate(db)
}
}
const val DBNAME: String = "bar.db"
}
}
- Again CallBacks do nothing
FooWithBar
class FooWithBar {
var foo: Foo
var bar: Bar
constructor(fooId: Long, fooDao: FooDao, barDao: BarDao) {
this.foo = fooDao.getFooById(fooId)
this.bar = barDao.getBarById(foo.barId)
}
}
- As you cannot get both a Foo and a Bar this does the equivalent of a join by getting the Foo via the FooDatabase and then gets the associated Bar via the BarDatabase.
MainActivity putting it altogether :-
class MainActivity : AppCompatActivity() {
lateinit var foodb: FooDatabase
lateinit var fooDao: FooDao
lateinit var bardb: BarDatabase
lateinit var barDao: BarDao
override fun onCreate(savedInstanceState: Bundle?) {
super.onCreate(savedInstanceState)
setContentView(R.layout.activity_main)
foodb = FooDatabase.getInstance(this)
fooDao = foodb.fooDao()
bardb = BarDatabase.getInstance(this)
barDao = bardb.barDao()
/* Add some data */
fooDao.insert(Foo(null,barDao.insert(Bar(null,"BAR1")),"FOO1"))
barDao.insert(Bar(null,"BAR UNUSED"))
fooDao.insert(Foo(null,barDao.insert(Bar(null,"BAR2")),"FOO2"))
/* Get equivalent of join (simple) using the FooWithBar */
val allFoosWithBars = mutableListOf()
for(foo: Foo in fooDao.getAllFoos()) {
allFoosWithBars.add(FooWithBar(foo.fooId!!,fooDao,barDao))
}
for(fwb: FooWithBar in allFoosWithBars) {
Log.d("FOOBARINFO","Foo is ${fwb.foo.fooText} Bar is ${fwb.bar.barText}")
}
//* Done with the Bar database Room wise
bardb.closeInstance()
foodb.attachBar(this) //<<<<< ATTACHES the Bar database to the Foo
/* Get a Supprort SQLite Database */
var sdb = foodb.openHelper.writableDatabase
/* Query Foo and the attached Bar */
var csr = sdb.query("SELECT * FROM foo JOIN ${FooDatabase.BAR_SCHEMA_NAME}.bar ON foo.barId = ${FooDatabase.BAR_SCHEMA_NAME}.bar.id")
DatabaseUtils.dumpCursor(csr)
csr.close()
}
}
Result
2021-06-16 16:35:04.045 D/FOOBARINFO: Foo is FOO1 Bar is BAR1
2021-06-16 16:35:04.045 D/FOOBARINFO: Foo is FOO2 Bar is BAR2
2021-06-16 16:35:04.092 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@ee9871b
2021-06-16 16:35:04.093 I/System.out: 0 {
2021-06-16 16:35:04.093 I/System.out: fooId=1
2021-06-16 16:35:04.093 I/System.out: barId=1
2021-06-16 16:35:04.093 I/System.out: fooText=FOO1
2021-06-16 16:35:04.093 I/System.out: id=1
2021-06-16 16:35:04.093 I/System.out: barText=BAR1
2021-06-16 16:35:04.093 I/System.out: }
2021-06-16 16:35:04.093 I/System.out: 1 {
2021-06-16 16:35:04.093 I/System.out: fooId=2
2021-06-16 16:35:04.093 I/System.out: barId=3
2021-06-16 16:35:04.093 I/System.out: fooText=FOO2
2021-06-16 16:35:04.093 I/System.out: id=3
2021-06-16 16:35:04.093 I/System.out: barText=BAR2
2021-06-16 16:35:04.094 I/System.out: }
2021-06-16 16:35:04.094 I/System.out: <<<<<
Related Topics
How to Restore a Dump File from MySQLdump
What Is Best Tool to Compare Two SQL Server Databases (Schema and Data)
SQL Case Sensitive String Compare
SQL Query: Delete All Records from the Table Except Latest N
How to Run a SQL Query on an Excel Table
Select Info from Table Where Row Has Max Date
Fastest Way to Remove Non-Numeric Characters from a Varchar in SQL Server
SQL Server - How to Lock a Table Until a Stored Procedure Finishes
Optimize Query With Offset on Large Table
How to Set Table Name in Dynamic SQL Query
Group by and Aggregate Sequential Numeric Values