Query regarding Full outer join

Question

I have two tables and joining them using full outer join.

Tab1:

Crossswalk	Ind	name
123	Y	abc

Tab2:

Crosswalk	Ind	name
123	Null	abc
123	Null	bcd

select coalesce(a.crosswalk,b.crosswalk), a.Ind, coalesce(a.name,b.name)
from Tab1 a
full join Tab2 b on a.crosswalk = b.crosswalk and a.name = b.name

I am getting result as

Crosswalk	Ind	name
123	Y	abc
123	Null	bcd

I want indicator field to populated with Y for both the rows as below.

Crosswalk	Ind	name
123	Y	abc
123	Y	bcd

this is a sample record. I have more records and Ind field should always populated with the Ind value from Tab1

Is the crosswalk unique in Tab1? For instance: what if there is a row 123, N, XYZ in tab1? In that case what will come up in your result for Ind? X or Y?? — Raseena Abdul, Mar 08 '21 at 18:36
its always unique inTab1. Each crosswalk will have one value only either N or Y — Mohan Babu, Mar 08 '21 at 18:37

score 0 · Answer 1 · answered Mar 08 '21 at 18:50

From your comment it looks like you need to show cross_walk and Ind from Tab1 and name from Tab2.

select a.crosswalk, a.Ind , b.name
from Tab1 a  join Tab2 b on a.crosswalk=b.crosswalk

Here is the DB Fiddle : https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=0145f950c2bfef3af7b983a229fc387d

Query regarding Full outer join

1 Answers1