Join On Two Different Table's Column Sqlite3

hi have a two table one table contain column named Table A _id, Eng, Hindi other table contain Table B _id, Eng, Hindi i want to join two table by matching word with Eng if Eng

Solution 1:

FULL JOIN is a term to combine rows from multiple table. Does not apply to your needs.

You only need to insert into table a all entries in table b which are not in table a.

INSERTINTOTABLE a(Eng, Hindi) SELECT Eng, Hindi FROM b WHERE eng NOTIN (SELECT eng FROM a);

Solution 2:

If you just want to get (SELECT) the desired output and TableA can contain records that are not present in TableB then you can emulate FULL JOIN to achieve your goal

SELECT e.eng, COALESCE(a.hindi, b.hindi) hindi
  FROM
(
  SELECT eng FROM TableB 
   UNIONSELECT eng FROM TableA
) e LEFTJOIN TableB b 
    ON e.eng = b.eng LEFTJOIN TableA a
    ON e.eng = a.eng

If on the other hand TableAalways contains only a subset of eng values of TableB then you can just use LEFT JOIN

SELECT b.eng, COALESCE(a.hindi, b.hindi) hindi
  FROM TableB b LEFTJOIN TableA a
    ON b.eng = a.eng

Here is SQLFiddle demo

---

Now if you want to update the content of TableA and assuming that A_id is AUTOINCREMENTyou can do

INSERTINTO TableA (eng, hindi)
SELECT b.eng, b.hindi
  FROM TableB b LEFTJOIN TableA a
    ON b.eng = a.eng
 WHERE a.eng ISNULL

Here is SQLFiddle demo

or

INSERTINTO TableA (eng, hindi)
SELECT b.eng, b.hindi
  FROM TableB b
 WHERENOTEXISTS
(
  SELECT*FROM TableA
   WHERE eng = b.eng
);

Here is SQLFiddle demo