T-SQL: Joining two tables

Question

I came across this problem and after going around and around I ended up getting stuck. I have two tables with some columns in common and I would like to merge them into one:

I am not looking for the code necessarily but rather for some guidance towards the answer.

please post expected result,actual result as text,not images — TheGameiswar
– TheGameiswar, Commented Sep 17, 2017 at 4:03
UNION ALL is a good start. Then you need aggregates to combine rows — Nick.Mc
– Nick.Mc, Commented Sep 17, 2017 at 12:54

zarruq · Accepted Answer · 2017-09-17 07:39:48Z

1

You can get your desired result by full join-ing both tables and using coalesce and case as below.

SELECT COALESCE(t1.user1, t2.user1) AS "User", 
       t1.monday, 
       CASE 
         WHEN t2.tuesday = t1.monday THEN NULL 
         ELSE t2.tuesday 
       END as Tuesday
FROM   table1 t1 
       FULL JOIN table2 t2 
              ON t1.user1 = t2.user1 
ORDER  BY "user";

Result:

User    monday  Tuesday
----------------------
user1     gym     run
user1     run     
user2     gym     run
user3             gym
user4             gym
user5     gym

You can heck the demo here

edited Sep 17, 2017 at 7:39

answered Sep 17, 2017 at 4:57

zarruq

2,4652 gold badges12 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

aeroalpha Over a year ago

That really helped a lot, but there is an issue: When user1 has run and gym on Mondays and Tuesdays, one of the exercises for Tuesday gets deleted by the CASE. If the case is removed then I duplicated rows.

zarruq Over a year ago

@aeroalpha: I worked through the sample data provided in question. If you are looking for an answer for a different data set, kindly add another question. we will be happy to help. Thanks :-)

Collectives™ on Stack Overflow

T-SQL: Joining two tables

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related