-1

I have two tables with the same schema, I want combine both the tables with distinct Id. If there is matching between two tables (with the same Id), then chose Table A Source over Table B Source

Table A                     Table B         
Id  Name    Age Source      Id  Name    Age Source
A   Jack    33  public      A   Jack    32  private
B   Jon     44  public      B   Jon     44  private
C   Tom     45  public      E   Matt    19  private
D   sid     19  public      F   Tom     30  private
A   Jack    33  public


    Result          
Id  Name    Age Source
A   Jack    33  public
B   Jon     44  public
C   Tom     45  public
D   sid     19  public
E   Matt    19  private
F   Tom     30  private

I tried union, but now sure how to prefer table Source over Table B source. Thanks for your help

GMB
  • 216,147
  • 25
  • 84
  • 135
Ullan
  • 905
  • 4
  • 15
  • 28
  • You want a `FULL OUTER JOIN` here – Thom A Mar 24 '23 at 15:00
  • Does this answer your question? [Query to merge 2 tables , not sure if this is a full outer join](https://stackoverflow.com/questions/63163828/query-to-merge-2-tables-not-sure-if-this-is-a-full-outer-join) – Thom A Mar 24 '23 at 15:03
  • 1
    Did you mean to have Jack in TableA twice? – DavidG Mar 24 '23 at 15:21

1 Answers1

0

This reads like a full join, and coalesce() for prioritization:

select coalesce(a.id, b.id) id,
    coalesce(a.name, b.name) name,
    coalesce(a.age, b.age) age,
    coalesce(a.source, b.source) source
from tablea a
full join tableb b on a.id = b.id
GMB
  • 216,147
  • 25
  • 84
  • 135
  • How do avoid the duplicates? My filter condition is just id and source. The above query is returning duplicate records if a.age is different than b.age – Ullan Mar 24 '23 at 16:38
  • @Ullan: no, the duplicates - if any - come from your tables, and it means that `id` is not unique in each table. Is this the case? – GMB Mar 24 '23 at 16:46
  • Yes, Id is not unique - – Ullan Mar 24 '23 at 16:58
  • Ok @Ullan, so if, for example, there are three rows in `tablea` for a given `id`, which one do you want to pick? – GMB Mar 24 '23 at 19:15