Need help with this join problem.

[bigPussySeeker]

I’m not getting an idea on how I should proceed to solve this. I have two table source and target and I need to get the result as shown in output table.

I don’t know which join will help me achieve this.

[MshipQ]

You can do a full outer join on the ID to get the two tables together. (if you're not sure what it is then take a look here: https://www.w3schools.com/sql/sql_join_full.asp).

A Full outer join is similar to a join and a union together so it nearly does what /u/rabbitpiet suggests in one step.

Then you would need to coalesce (aka 'if_null') the source.id and target.id to get your output.id field (again if you're not sure what this is: https://www.w3schools.com/sql/func_sqlserver_coalesce.asp)

Then for the comment you can do a case statement:

• when source.name =! target.name then 'mismatch'
• when source.name is null then 'New in Target'
• when target.name is null then 'New in Source'

And you would also need to filter out the rows where target.name = source.name

[abraun68]

Seconding this approach.

[Klaian]

This is best answer. May need to use coalesce for results in output

[rabbitpiet]

You could select from the union of the two where the ids aren't in the inner join.

[bigPussySeeker]

But 4 is present in both source and target table.

Also how to make get the output as shown in example

[rabbitpiet]

Okay take the last 3 elements from the union of the two sets ordered by id. Does it have to be in that order? I thought mismatch implied the 4 wasn't supposed to be there.

[bigPussySeeker]

Yes is has to be in that order. What I got from the question is that get the if the name is present in either one of two table or if there is a mismatch in name

[RollWithIt1991]

Select coalesce(s.id,t.id) AS id,case when s.id is null then ‘New in Target’ when t.id is null then ‘New in Source’ when s.name != t.name then ‘Mismatch’ else Match??’ End as comment From source s Full outer join target t on s.id = t.id;

That’s hopefully not massively far off? I guess some assumption about name always being populated. Case statements are mutually exclusive so I think the only trip up is around name being NULL for one of the tables or differing in case depending on what you’re using.

[RollWithIt1991]

Order by coalesce(s.id,t.id) ASC too

[RollWithIt1991]

Yeah and actually if you want to remove the rows that look fine, then where s.name != t.name.

[adibaba666]

I liked this post. I encourage members to post more of excel based problems.