CODEWITHSUNDEEP
sqlsql-serverunion-all
skt
skt

Reputation: 589

How to select Distinct UNION with priority 1st table

How to write the distinct Union query with ignore one column in all the tables.

I would like to select the data from two tables with same field name and insert into 3rd table but data should be unique except one field (1st table row number should consider)

CREATE TABLE table_a
(
    id INTEGER ,
    name TEXT,
    rownumber TEXT
) ;
CREATE TABLE table_b
(
    id INTEGER ,
    name TEXT,
    rownumber TEXT
) ;

CREATE TABLE table_c
(
    id INTEGER,
    name TEXT,
    rownumber TEXT
) ;

INSERT INTO 
   table_a
   (id, name, rownumber)
VALUES
   (1001, 'Alex' , '111'),
   (1002, 'John' , '112'),
   (1003, 'Max'  , '113'),
   (1004, 'Joly'  , '114'), 

INSERT INTO 
   table_b
   (id, name, rownumber)
VALUES
   (1005, 'Angila', '211'),
   (1002, 'John'  , '212'),
   (1006, 'Tim'   , '213'),

Expected Table_c Should be unique id and name from "table_a" + "table_b" and "rowversion" should consider from the table_a

Result as :-

   (1001, 'Alex' , '111'),
   (1002, 'John' , '112'),  // From 1st table
   (1003, 'Max'  , '113'),
   (1004, 'Joly'  , '114'),

   (1005, 'Angila', '211'),
   (1006, 'Tim'   , '213'),

Upvotes: 0

Views: 1739

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

How about using not exists?

select a.*
from table_a a
union all
select b.*
from table_b b
where not exists (select 1 from table_a a where a.id = b.id and a.name = b.name);

Upvotes: 5

Related Questions