Merging Two Sqlite Databases Which Both Have Junction Tables

Merging two SQLite databases which both have junction tables

If you don't care about duplicates, you could get the maximum ID from DB1, and add it to every ID in DB2.
However, you said that name could be unique, so let's do this right.

I'm assuming that all id columns are INTEGER PRIMARY KEY, i.e., autoincrementing.

Open DB1, and attach DB2:

ATTACH '...' AS db2;

Copy over all fruits and juices that do not yet exist in DB1 (these get new IDs):

INSERT INTO Fruit(name)
SELECT name
FROM db2.Fruit
WHERE name NOT IN (SELECT name
                   FROM Fruit);
INSERT INTO Juice(name)
SELECT name
FROM db2.Juice
WHERE name NOT IN (SELECT name
                   FROM Juice);

Now copy over the recipes, while looking up the new ID values through the corresponding name:

INSERT INTO Recipe(juice_id, fruit_id)
SELECT (SELECT id
        FROM Juice
        WHERE name = (SELECT name
                      FROM db2.Juice
                      WHERE id = Recipe2.juice_id)),
       (SELECT id
        FROM Fruit
        WHERE name = (SELECT name
                      FROM db2.Fruit
                      WHERE id = Recipe2.fruit_id))
FROM db2.Recipe AS Recipe2;

Sqlite merging databases into one, with unique values, preserving foregin key relation

PRAGMA foreign_keys = on;

ATTACH DATABASE 'db1.sqlite' AS db1;

ATTACH DATABASE 'db2.sqlite' AS db2;

BEGIN;

CREATE TABLE Fruit      (
                          id            INTEGER PRIMARY KEY NOT NULL,
                          name          TEXT    UNIQUE ON CONFLICT IGNORE
                          )
                          ;

CREATE TABLE Juice      (
                          id            INTEGER PRIMARY KEY NOT NULL,
                          name          TEXT    UNIQUE ON CONFLICT IGNORE
                        )
                        ;

CREATE TABLE Recipe     (
                          id            INTEGER PRIMARY KEY NOT NULL,
                          juice_id      INTEGER NOT NULL,
                          fruit_id      INTEGER NOT NULL,
                          FOREIGN KEY   ( juice_id ) REFERENCES Juice ( id )
                                        ON UPDATE CASCADE
                                        ON DELETE CASCADE,
                          FOREIGN KEY   ( fruit_id ) REFERENCES Fruit ( id )
                                        ON UPDATE CASCADE
                                        ON DELETE CASCADE
                        )
                        ;

INSERT INTO Fruit  ( id, name )               SELECT id, name FROM db1.Fruit;
INSERT INTO Juice  ( id, name )               SELECT id, name FROM db1.Juice;
INSERT INTO Recipe ( id, juice_id, fruit_id ) SELECT id, juice_id, fruit_id FROM db1.Recipe;

INSERT INTO Fruit ( name ) SELECT name FROM db2.Fruit;
INSERT INTO Juice ( name ) SELECT name FROM db2.Juice;

CREATE TEMPORARY TABLE Recipe_tmp AS
                                    SELECT Juice.name AS j_name, Fruit.name AS f_name
                                      FROM db2.Recipe, db2.Fruit, db2.Juice
                                        WHERE db2.Recipe.juice_id = db2.Juice.id AND db2.Recipe.fruit_id = db2.Fruit.id
;

INSERT INTO Recipe ( juice_id, fruit_id ) SELECT j.id, f.id
                                            FROM Recipe_tmp AS r, Juice AS j, Fruit AS f
                                              WHERE r.j_name = j.name AND r.f_name = f.name
;

DROP TABLE Recipe_tmp;

COMMIT;

DETACH DATABASE db1;
DETACH DATABASE db2;

Use SQLite SELECT to combine joined table column into one row for each category using a junction table

You must join Categories to Reference first and then to Items, with LEFT joins, just in case a category does not have any items related and then aggregate with GROUP_CONCAT():

SELECT c.name,
       GROUP_CONCAT(i.name || ' - ' || i.status, ', ') Related
FROM Categories c
LEFT JOIN Reference r ON r.cat_id = c.id
LEFT JOIN Items i ON i.id = r.item_id
GROUP BY c.id, c.name

See the demo.

Results:

| name  | Related                        |
| ----- | ------------------------------ |
| wet   | river - north fork, lake - big |
| dry   | desert - mojave                |
| metal | car - ford, truck - chevy      |

Combine data from two different junction tables

Looks almost ok but you need to assign the category_id in the left outer join as well. Otherwise you get all featured items of the item:

SELECT i.*, category.name, category_feat_item.item_id AS featured
FROM item AS i
INNER JOIN item_category ON i.id = item_category.item_id
INNER JOIN category ON category.id = item_category.category_id AND category.id =1
LEFT OUTER JOIN category_feat_item ON i.id = category_feat_item.item_id AND category_feat_item.category_id = 1
ORDER BY featured DESC

Android Room Many to Many Junction table getting more than relation of 2 tables

As far as I know, unfortunately there is no out-of-the-box way, how you can solve your use case with just Room's tools (@Relation, @Embedded, @Junction).

My first question is as far as i have seen, not much experience with junction or associative tables, they only store foreign keys for the tables that should associate with, is it okay to add value fields to junction table?

The thing is @Junction has some restriction in use - it just helps to bind two tables with the values that kept in third (junction) table. But @Relation-with-@Junction API doesn't support getting from this junction table any field to include to result class (these foreign keys are used only for binding). That's why technically you can add some fields to the junction table (and it seems it's the most suitable place where you should keep these values), but in practice you just couldn't get these fields using @Junction.

Maybe there is some hack-ish way you could use for that, but my guess - you'll have to implement your own method with SQL joins and passing result with loops to form needed result (similar to that was implemented in the link you mentioned in your post).

As a simplification to your case you could really describe Entity-junction table as you suggested (but not to use @Junction at all):

data class UserPropertyJunction(
    val userAccountId: Long,
    val propertyId: Int,
    val displayCount:Int=0,
    val favorite:Boolean=false
)

and then to add auxiliary class (not entity) and use it as a query result:

data class UserWithFavorites(
    val displayCount:Int,
    val favorite:Boolean,
    @Relation(
         parentColumn = "userAccountId",
         entityColumn = "userId"
    )
    val user: UserEntity,
    @Relation(
         parentColumn = "propertyId",
         entityColumn = "id"
    )
    val property: Property,
)

Of course, that's not what you want but at least you can deal with that, it's out-of-the-box and you can use LiveData/Flow/RxJava with that (for example, after getting this you could try to use some transformation operators to somehow change it to needed format)

UPDATE (for simplified version)

If you don't need users in result, but you just want to filter by userId then your auxiliary class could be as follows:

data class PropertiesWithFavorites(
    val displayCount:Int,
    val favourite:Boolean,
    val propertyId: Long,
    @Relation(
         parentColumn = "propertyId",
         entityColumn = "id"
    )
    val property: Property
)

And dao method:

@Query("SELECT * FROM user_property_junction as j where j.userAccountId =:userId")
fun getPropertiesByUser(userId: Long): List<PropertiesWithFavorites>  

Junction table joining multiple tables

You could use exclusive FKs or inheritance as described in this post.

If you opt for the former, the CHECK employed for exclusive FKs would need to use slightly different syntax (from what was used in the link) to be practical:

CHECK (
    (
        CASE WHEN UserID         IS NULL THEN 0 ELSE 1 END
        + CASE WHEN TableID      IS NULL THEN 0 ELSE 1 END
        + CASE WHEN FoodID       IS NULL THEN 0 ELSE 1 END
        + CASE WHEN RestaurantID IS NULL THEN 0 ELSE 1 END
        + CASE WHEN CategoryID   IS NULL THEN 0 ELSE 1 END
        + CASE WHEN ShipID       IS NULL THEN 0 ELSE 1 END
    )
    = 1
)

The CHECK above ensures only one of the FKs is non-NULL at any given time.

BTW, your suspicions about "dynamic" FK (with the "type" field) were correct.

SQL - How to join 2 tables with combined SUM function ? (Incorrect results currently)

I don't think you want a full join. You would if both tables could be joined together directly, but the fact that we need to to through the junction table changes the situation.

We can start from the junction table, then union both grocery tables (while separating the original columns) and bring them with a left join on both possibles matches. The last step is aggregation:

select j.name1, sum(t.amount1) as grocery1, sum(t.amount2) as grocery2
from junction1 j
left join (
    select id1, null as id2, amount as amount1, null as amount2 from test1
    union all select null, id2, null, amount from test2
) t on t.id1 = j.id1 or t.id2 = j.id2
group by j.name1

Using union avoids the "row multiplication" issue that you are seeing. On the other hand, the left join ensures that records from both tables are preserved, regardless of whether or not the same person actually shopped at both locations.

SQLite: retrieving columns from two parent tables with JOIN

The language that you're using to access the database (whatever it is) does not allow accessing columns by their index, and gets confused by the identical column names.

Rename the result columns with AS:

SELECT 
    schoolA_courses.course_prefix AS A_prefix,
    schoolA_courses.course_number AS A_number,
    schoolA_courses.course_title  AS A_title,
    schoolB_courses.course_prefix AS B_prefix,
    ...

Join two tables together with the use of a third table that contains composite keys from both?

The problem here is your design; you need to fix it. You are storing a delimited value in your column, combinedcode, in your junction table.

What you should be doing is storing the 2 values in separate columns, and then creating your foreign keys on those values. This would look something like this:

CREATE TABLE dbo.Table1 (SomeID varchar(10) NOT NULL,
                         SomeValue varchar(20));
ALTER TABLE dbo.Table1 ADD CONSTRAINT PK_Table1 PRIMARY KEY (SomeID);
GO
CREATE TABLE dbo.Table2 (OtherID varchar(10) NOT NULL,
                         OtherValue varchar(20));
ALTER TABLE dbo.Table2 ADD CONSTRAINT PK_Table2 PRIMARY KEY (OtherID);
GO
CREATE TABLE dbo.JunctionTable (SomeID varchar(10) NOT NULL,
                                OtherID varchar(10) NOT NULL);
ALTER TABLE dbo.JunctionTable ADD CONSTRAINT FK_JunctionTable1 FOREIGN KEY (SomeID) REFERENCES dbo.Table1(SomeID);
ALTER TABLE dbo.JunctionTable ADD CONSTRAINT FK_JunctionTable2 FOREIGN KEY (OtherID) REFERENCES dbo.Table2(OtherID);

Depending on your design, you may want to make it so that the value in the junction table are are unique:

ALTER TABLE dbo.JunctionTable ADD CONSTRAINT PK_JunctionTable PRIMARY KEY (SomeID,OtherID);

Then, to do your JOINs it would be as simple as:

SELECT {Your Columns}
FROM dbo.Table1 T1
     JOIN dbo.JunctionTable JT ON T1.SomeID = JT.SomeID
     JOIN dbo.Table2 T2 ON JT.OtherID = T2.OtherID;

Join 3 tables in SQLite database

Using an explicit JOIN instead of an implicit one, the following should get what you want, although it is curious that your implicit join syntax did not return correct results in the first place. I have used a LEFT JOIN, to account for songs which do not have an associated artist or album, but that may not be necessary for your data set and an INNER JOIN could be used instead.

I have also added column aliases to eliminate ambiguity when fetching rows, since you have similar column names in most of your tables (id, name).

SELECT
  Songs._id AS song_id,
  Songs.name AS song_name, 
  Songs.length, 
  Songs.artist_id AS artist_id, 
  Artists.name AS artist_name, 
  Songs.album_id AS album_id, 
  Albums.name AS album_name
FROM 
 Songs 
 LEFT JOIN Artists ON Songs.artist_id = Artists._id
 LEFT JOIN Albums ON Songs.album_id = Albums._id

---

Related Topics