CODEWITHSUNDEEP
sqlsqliteandroid-sqlite
Siddhpura Amit
Siddhpura Amit

Reputation: 15128

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 word is not match it will be added into Table A which is from Table B otherwise Table A's value will be remain

for example

Now Table B

enter image description here

Table A

enter image description here

The Result would be on TABLE A is

enter image description here

Upvotes: 0

Views: 453

Answers (2)

peterm
peterm

Reputation: 92845

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 
   UNION 
  SELECT eng FROM TableA
) e LEFT JOIN TableB b 
    ON e.eng = b.eng LEFT JOIN TableA a
    ON e.eng = a.eng

If on the other hand TableA always 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 LEFT JOIN 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

INSERT INTO TableA (eng, hindi)
SELECT b.eng, b.hindi
  FROM TableB b LEFT JOIN TableA a
    ON b.eng = a.eng
 WHERE a.eng IS NULL

Here is SQLFiddle demo

or 

INSERT INTO TableA (eng, hindi)
SELECT b.eng, b.hindi
  FROM TableB b
 WHERE NOT EXISTS
(
  SELECT * 
    FROM TableA
   WHERE eng = b.eng
);

Here is SQLFiddle demo

Upvotes: 1

LS_ᴅᴇᴠ
LS_ᴅᴇᴠ

Reputation: 11181

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.

INSERT INTO TABLE a(Eng, Hindi) SELECT Eng, Hindi FROM b WHERE eng NOT IN (SELECT eng FROM a);

Upvotes: 2

Related Questions