Joining two tables but if column contains nulls join using another column for that row

[Porkey]

I dont think this is possible, but i was wondering if it is, ive looked round but cant find an example of it being or not being possible

Merge 1 with 2

Table 1

Staff ID	Department ID
1	a
2	c
null	b

Table 2

Staff ID	Depatment ID	Location
1	a	Scotland
2	c	Wales
3	b	England

Outcome

Staff ID	Department ID	Location
1	a	Scotland
null	b	England
2	c	Wales

So because department B is in england it picks that up rather than where the staff id is based, I wouldnt want to use department ID as certain staff would not be located in the department location so would prefer to pick it up from Staff ID, but where thats missing from department ID instead

[v-rongtiep-msft · Accepted solution]

Hi @Porkey ,

Please try to use Right Outer join kind.

Then remove the columns that you don't need.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.