1

Can anyone please help me on how to create view joining two tables having same columns without duplicates?

Example:

I have two tables T1 and T2

T1

Id    Name    Date
-----------------------
1     AAA     2019-04-05
2     BBB     2019-04-06
3     CCC     2019-04-07

T2

Id    Name    Date
----------------------
4     DDD     2019-04-01
1     ABC     2019-03-01
2     DEF     2019-03-02

My output view should look like this

Id    Name     Date
------------------------
1     AAA     2019-04-05  (From T1)
2     BBB     2019-04-06  (From T1)
3     CCC     2019-04-07  (From T1)
4     DDD     2019-04-01  (From T2)

Below is the query I am trying

CREATE VIEW view AS (
  (
    SELECT
      t1.id,
      t1.name,
      t1.date,
    FROM
      T1 as t1
    UNION 
    SELECT
      t2.id,
      t2.name,
      t2.date,
    FROM
      T2 as t2
  )

But I get duplicate records.

jarlh
  • 42,561
  • 8
  • 45
  • 63
LHS
  • 77
  • 5

1 Answers1

2

You seem to want a prioritization query, with table1 prioritized over table2. In MySQL:

select t1.id, t1.name, t1.date
from table1 t1
union all
select t2.id, t2.name, t2.date
from table2 t2
where not exists (select 1 from table1 t1 where t2.id = t1.id);

This selects all rows from table1 and then selects all rows from table2 that don't have a matching id in table1.

This assumes that id is sufficient for determining which records are "duplicates".

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thank you for replying. What does this statement mean where not exists (select 1 from table1 t1 where t2.id = t1.id);? Also which condition above ensures that if the record is found in T1 that takes the priority? – LHS Jun 14 '19 at 03:46