Resolve union of 2 tables that have duplicate primary key by picking only 1 of the rows and skip the-CodePudding

I have 2 tables and i am joining them using the below Query

Select distinct EmailAddress,CUSTOMER_ID,Send_Date,Unique_key,sub_category
from table1

UNION ALL

Select distinct EmailAddress,CUSTOMER_ID,Send_Date,Unique_key,sub_category
from table2

I am using Unique_key as the primary key. It is the concatination of send date customer id. Sometimes both the tables can have duplicate keys and hence I want to take only 1row in such cases using the above query

Table 1                 
EmailAddress       CUSTOMER_ID  Send_Date   Unique_key  sub_category
a@gmail.com        1001         07-08-2021  70820211001 chair
                
                
Table 2             
EmailAddress       CUSTOMER_ID  Send_Date   Unique_key  sub_category
a@gmail.com        1001         07-08-2021  7082021100  book

What is expected results ?

EmailAddress    CUSTOMER_ID Send_Date   Unique_key  sub_category
a@gmail.com     1001        07-08-2021  70820211001 chair

Only 1 record should appear in the final table & multiple rows should be skipped. I don't want to change anything in unique key format. Is there any workaround for this?

CodePudding user response：

You need something like:

Select distinct EmailAddress,CUSTOMER_ID,Send_Date,sub_category
from table_1
UNION 
SELECT EmailAddress,CUSTOMER_ID,Send_Date,sub_category FROM table_2 
WHERE NOT EXISTS ( SELECT NULL 
                   FROM table_1 
                   WHERE table_1.EmailAddress = table_2.EmailAddress ) ;

The below select will return empty set, because you have the WHERE NOT EXISTS condition, return the non matching row.

SELECT EmailAddress,CUSTOMER_ID,Send_Date,sub_category 
FROM table_2 
WHERE NOT EXISTS ( SELECT NULL 
                   FROM table_1 
                   WHERE table_1.EmailAddress = table_2.EmailAddress 
                  ) ;

Demo: https://www.db-fiddle.com/f/pB6b5xrgPKCivFWcpQHsyE/24

Try with your data and let me know.