CODEWITHSUNDEEP
sqlsql-serverjoin
New_here
New_here

Reputation: 85

Merge two table and pick data based on table order

I'm trying to merge 2021 tbl with 2020 table. If the same record(id) is common in two tables then i go for 2021 table, if the id is not present in 2021 then go for 2020 and if the id is not present in 021 and in 020 then 020 record is selected. I tried to address this using coalesce but it's not helping. Any suggestions on improving this situation.

2021_table

|num| name | location | age | gender | foundation | relation | email |
| ---|------|----------|-----|--------|------------|----------|--------
| 1 | ally |  texas   | 55  | F | NA  | mom | null          |
|2  | rick |  newyork | 45  | M | NA  | dad | [email protected]|
|3  | macy |  cali    | 66  | F | EY  | na  | [email protected]|
|8A | steve|  ark     | 39  | M | JJ  | xyz | [email protected]|
 

 2020_table
 
| num| name | location | gender | org | status | descent |
| ----|------|----------|-----|--------|-----|--------|-------|
|1  | ally |  texas   |  F  |  |  |
|2  | r    |  newyork |  M  |  |  |
|78 | naomy|  NULL    |  F  |  |  |
|B12| romy | CO       |  M  |  |  |
 

Output_table:

| num| name | location | age | gender | foundation | relation | email |
| ----|------|----------|-----|--------|------------|----------|---|
| 1  | ally |  texas   | 55  | F | NA  | mom | null           |
| 2  | rick |  newyork | 45  | M | NA  | dad | [email protected]|
|3   | macy |  cali    | 66  | F | EY  | na  | [email protected] |
|8A  | steve|  ark     | 39  | M | JJ  | xyz | [email protected] |
| 78 | naomy|  NULL    | 30  | F |     |     |                |
| B12| romy | CO       | 69  | M |     |     |                |

Upvotes: 1

Views: 51

Answers (2)

jim
jim

Reputation: 456

The query below should meet the rules stated:

  • if id is common in two tables then go for 2021 table,
  • if the id is not present in 2021 then go for 2020 and
  • if the id is not present in 021 and in 020 then 020 record is selected

The tables are not identical, so I set NULLs for the 2020 table for columns not existing in the 2021 table. Empty string ( '' ) also works.

SELECT num, name , location , age , gender , foundation , relation , email FROM t2021

UNION 

SELECT num, name , location , age , gender , NULL foundation , NULL relation , NULL email FROM t2020
WHERE num NOT IN  ( SELECT num FROM t2021 )

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269923

You can use union all and exists:

select t2021.num, t2021.name, t2021.location, t2021.age, t2021.gender, t2021.foundation, t2021.relation, t2021.email
from table_2021 t2021
union all
select t2020.num, t2020.name, t2020.location, t2020.age, t2020.gender, NULL, NULL, NULL
from table_2020 t2020
where not exists (select 1
                  from table_2021 t2021
                  where t2021.num = t2020.num
                 );

Note that having two tables with the same columns is usually an indication that you have a problem with your data model. I would recommend putting all the data in a single table, with an additional column for the year.

Upvotes: 1

Related Questions