Changing record values based on whether there are duplicates when two tables are combined-CodePudding

I know I can join Table #1 and Table #2 with a UNION and then filter out duplicate Id's using DISTINCT. However, for the duplicate contacts I'd like to change DrinkPreference to Coke/Pepsi.

Is this possible?

Starting Table #1

Id	FirstName	LastName	DrinkPreference
123	Tom	Bannon	Pepsi
124	Sarah	Smith	Pepsi

Starting Table #2

id	FirstName	LastName	DrinkPreference
125	Jim	Henry	Coke
123	Tom	Bannon	Coke

Table? #3 - combined with DrinkPreference set to Coke/Pepsi where contact exists in both tables?

Id	FirstName	LastName	DrinkPreference
125	Jim	Henry	Coke
123	Tom	Bannon	Coke/Pepsi
124	Sarah	Smith	Pepsi

CodePudding user response：

Achievable using multiple unions and joins.

select distinct FirstName, LastName, case when ct = 2 then 'Coke/Pepsi' else DrinkPreference end
from (
    select FirstName, LastName, DrinkPreference, Id from table1
    union all
    select FirstName, LastName, DrinkPreference, Id from table2) a 
left join
(
    select count(1)ct, Id from
        (select Id from table1
        union all
        select Id from table2) t1
    group by Id
    ) b on b.Id = a.Id

CodePudding user response：

You can try this one

SELECT coalesce(t1.firstname, t2.firstname) AS firstname,coalesce(t1.lastname,t2.lastname) AS lastname, CASE WHEN t1.drinkpreferences IS NULL THEN  t2.drinkpreferences  WHEN t2.drinkpreferences IS NULL THEN  t1.drinkpreferences
 ELSE  t1.drinkpreferences || '/' || t2.drinkpreferences END AS drinkpreferences FROM table1 t1  FULL JOIN table1 t2   ON t1.id = t2.id