"no such table" error when attempting a join function with two different tables in sqflite

I'm having quite a few problems understanding how sqflite works. I have the following "users" and "items" tables and their create method:

//tableName = "items"
Future<void> createTable(Database database) async {
  await database.execute("""CREATE TABLE IF NOT EXISTS $tableName (
    "itemId" INTEGER NOT NULL,
    "itemName" TEXT NOT NULL,
    "itemType" TEXT NOT NULL,
    "itemSubType" TEXT NOT NULL,
    PRIMARY KEY ("itemId" AUTOINCREMENT)
    );""");
}

//tableName = "users"
Future<void> createTable(Database database) async {
  await database.execute("""CREATE TABLE IF NOT EXISTS $tableName (
    "username" TEXT NOT NULL,
    "mail" TEXT NOT NULL,
    "password" TEXT NOT NULL,
    "birthdate" INTEGER NOT NULL
    PRIMARY KEY ("username")
  );""");
}

And then I have the junction table "user_items":

//tableName = "userItems"
Future<void> createTable(Database database) async {
  await database.execute("""
    CREATE TABLE IF NOT EXISTS $tableName (
    "username" TEXT NOT NULL,
    "itemId" INTEGER NOT NULL,
    "quantity" INTEGER NOT NULL,
    PRIMARY KEY ("username", "itemId")
    FOREIGN KEY (username) REFERENCES users
    FOREIGN KEY (itemId) REFERENCES items
    );
  """);
}

I have a separate database service class that handles database execution, they are created correctly and every query I've wrote has been working fine. All except the ones including a join function, like this one I have in the user_item table class:

//tableName = "userItems"
Future<List<UserItems>> fetchUserItems(username) async {
  final database = await DatabaseService("$tableName.db").database;
  final userItems = await database.rawQuery(
    '''
    SELECT itemName
    FROM items
    INNER JOIN $tableName ON $tableName.itemId = items.itemId
    WHERE $tableName.username = ?
    ORDER BY itemName ASC
    ''', [username]
);
return userItems.map((userItems) => UserItems.fromSqfliteDatabase(userItems)).toList();
}

This function gives an exception of "no such table: items". I understand the problem is withing the database.execution that is done upon the userItems table, but not on the items table. What is the correct way of doing this type of code on Flutter?

Solution

Typing this out made me realize the issue. I have created a file for each individual table, so each table has its own xtable.db file. This was a poor understanding of how database files work on my part.

There are two possible solutions:

I could rewrite the code and create all the tables inside the same file, then use a tailored join function like this:

Future<List<Map<String, dynamic>>> joinTables(Database db, String table1, String table2, String col1, String col2) async {
  // Construct the SQL query with JOIN clause
  final sql = '''
    SELECT *
    FROM $table1
    INNER JOIN $table2
    ON $table1.$col1 = $table2.$col2
  ''';

  // Execute the raw query and return the results
  final results = await db.rawQuery(sql);
  return results.cast<Map<String, dynamic>>();
}

or, I could keep the project as it is and do an attach/detach function as a workaround like so:

await db.execute('ATTACH DATABASE "$secondDbPath" AS secondDb');

At the moment of typing this out I haven't tested either solution, but let me know if any of this sounds good.