SQLite - How do you join tables from different databases?

Question

I have an application that uses a SQLite database and everything works the way it should. I'm now in the process of adding new functionalities that require a second SQLite database, but I'm having a hard time figuring out how to join tables from the different databases.

If someone can help me out with this one, I'd really appreciate it!

Edit: See this question for an example case you can adapt to your language when you attach databases as mentioned in the accepted answer.

Solution 1

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".

Solution 2

Here is a C# example to complete this Question

/// <summary>
/// attachSQL = attach 'C:\\WOI\\Daily SQL\\Attak.sqlite' as db1 */
/// path = "Path of the sqlite database file
/// sqlQuery  = @"Select A.SNo,A.MsgDate,A.ErrName,B.SNo as BSNo,B.Err as ErrAtB from Table1 as A 
///                    inner join db1.Labamba as B on 
///                    A.ErrName = B.Err";
/// </summary>
/// <param name="attachSQL"></param>
/// <param name="sqlQuery"></param>
public static DataTable GetDataTableFrom2DBFiles(string attachSQL, string sqlQuery)
{
    try
    {
        string conArtistName = "data source=" + path + ";";
        using (SQLiteConnection singleConnectionFor2DBFiles = new SQLiteConnection(conArtistName))
        {
            singleConnectionFor2DBFiles.Open();
            using (SQLiteCommand AttachCommand = new SQLiteCommand(attachSQL, singleConnectionFor2DBFiles))
            {
                AttachCommand.ExecuteNonQuery();
                using (SQLiteCommand SelectQueryCommand = new SQLiteCommand(sqlQuery, singleConnectionFor2DBFiles))
                {
                    using (DataTable dt = new DataTable())
                    {
                        using (SQLiteDataAdapter adapter = new SQLiteDataAdapter(SelectQueryCommand))
                        {
                            adapter.AcceptChangesDuringFill = true;
                            adapter.Fill(dt);
                            return dt;
                        }
                    }
                }
            }
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show("Use Process Exception method An error occurred");
        return null;
    }

}

Solution 3

Well, I don't have much experience with SQLite you have to access both databases in a single query.

You can have something like :

select name from DB1.table1 as a join DB2.table2 as b where a.age = b.age;

In databases like SQLServer you can access other databases in this hierarchical fashion, this should also work for SQLite.

I think you can initiate an instance of sqlite with more than 1 databases !

Comments

[Alex R.]

What are the database like? Are there any common columns that can be used to join them? Are the columns for each the same such that you can use an union? sqlite.org/syntaxdiagrams.html

[Adam Smith]

Yes there are columns that are joinable using the USING keyword as they are named the same. My problem is not that I don't know how to join since my program already does this frequently on tables in the same database, it's that I can't seem to find how to link both databases so one's data is usable from the other (like a join, for example)

[Adam Smith]

Example: the first database has a table called "schedule", it contains, among other columns, a date column, a team ID and a lane number. The second database has a table that keep tracks of scores entered by users for their teams game. So this table also has a date and a teamID. I want to join them using those two columns to know which lane every team is supposed to play on. There are other tables that will have to be joined for other purposes, but you can get an idea of what I need from this example.

[Adam Smith]

Yeah I saw the documentation for SQL server, but couldn't find the equivalent query for SQLite. The problem with that query is that I use a drivermanager to create my connection, so I have two connection objects which point to the database files, but doing conn1.table doesn't seem to be working for some reason.

[bkribbs]

User StanleyD noted that it didn't work for him until he put ' (single quotes) around the file name. I found the same.

[topsail]

This works - thanks. But as an aside, I don't think we need so many nested using statements.

[mvorisek]

Can I select different main database after attach statement?