How to join two SQLite Tables where key is not exact the same int value?-CodePudding

I have two tables, where the on joining key is not exact the same integer value. How can I join with the best match (the difference should be minimal) on these keys? Here is my Table Demo Dump.

     CREATE TABLE t1(
       "size" TEXT,
       filename TEXT
     );
     
     -- ----------------------------
     -- Records of matching
     -- ----------------------------
     INSERT INTO "main"."t1" VALUES (1162775, file1);
     INSERT INTO "main"."t1" VALUES (1145387, file1);
     INSERT INTO "main"."t1" VALUES (1388613, file1);
     INSERT INTO "main"."t1" VALUES (1306413, file1);
     INSERT INTO "main"."t1" VALUES (1792882, file1);
     INSERT INTO "main"."t1" VALUES (1798382, file1);
     INSERT INTO "main"."t1" VALUES (878147,  file1);
     INSERT INTO "main"."t1" VALUES (2614277, file1);
     INSERT INTO "main"."t1" VALUES (838639,  file1);
     INSERT INTO "main"."t1" VALUES (3053906, file1);
     INSERT INTO "main"."t1" VALUES (1019579, file1);
     INSERT INTO "main"."t1" VALUES (3234508, file1);
     INSERT INTO "main"."t1" VALUES (2442681, file1);

     CREATE TABLE t2(
        "info" Text,
        readysize TEXT
     );
     
     -- ----------------------------
     -- Records of matching
     -- ----------------------------
     INSERT INTO "main"."t2" VALUES (info1, 1162780);
     INSERT INTO "main"."t2" VALUES (info1, 1145392);
     INSERT INTO "main"."t2" VALUES (info1, 1388620);
     INSERT INTO "main"."t2" VALUES (info1, 1306420);
     INSERT INTO "main"."t2" VALUES (info1, 1792888);
     INSERT INTO "main"."t2" VALUES (info1, 1798388);
     INSERT INTO "main"."t2" VALUES (info1, 878152 );
     INSERT INTO "main"."t2" VALUES (info1, 2614284);
     INSERT INTO "main"."t2" VALUES (info1, 838644 );
     INSERT INTO "main"."t2" VALUES (info1, 3053912);
     INSERT INTO "main"."t2" VALUES (info1, 1019584);
     INSERT INTO "main"."t2" VALUES (info1, 3234516);
     INSERT INTO "main"."t2" VALUES (info1, 2442688);


These key relation should be obtained as best fit:

 1162775 ->  1162780
 1145387 ->  1145392
 1388613 ->  1388620
 1306413 ->  1306420        
 1792882 ->  1792888
 1798382 ->  1798388
 878147 ->    878152 
 2614277 ->  2614284
 838639 ->    838644
 3053906 ->  3053912
 1019579 ->  1019584
 3234508 ->  3234516
 2442681 ->  2442688

I would like that the on part of select * from t1 join t2 on t1.size (match best fit) t2.readysize so that for the first line the on should match 1162775 to 1162780 and so on.

PS: Well I think the on key could like this be done:

    select * from t1 join t2 on abs(t1.size - t2.readysize) < 10 and ((t1.size >= t2.readysize) or (t1.size <= t2.readysize))

would this make sense? So I have defined a delta which is max 10 f.e. and the absolut position by the and limiting.

CodePudding user response：

Use a CROSS JOIN of the tables and aggregation, utilizing SQLite's feature of bare columns:

SELECT *, 
       MIN(ABS(t1.size - t2.readysize)) AS diff
FROM t1 JOIN t2
GROUP BY t1.rowid;

You may change rowid with the primary key of t1.

See the demo.