SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Create Table RolesTable(SchemaId varchar(50),RoleId varchar(50),
RoleName varchar(20),Tenant varchar(20));

Create Table FormSchema(SchemaId varchar(50),SchemaName varchar(20),Tenant varchar(20))
Insert into RolesTable(SchemaId,RoleId,RoleName,Tenant)
 values
 ('123','40ecca83-7fd9-4d63-9f56-c7a48442d844','#Test-1','A'),
 ('xyz','40ecca83-7fd9-4d63-9f56-c7a48442d844','#Test-1','All'),
 ('456','40ecca83-7fd9-4d63-9f56-c7a48442d844','#Test-1','B'),
 ('abc','40ecca83-7fd9-4d63-9f56-c7a48442d844','#Test-1','A'),
 ('664','40ecca83-7fd9-4d63-9f56-c7a48442d844','#Test-1','B');
 
 Insert into FormSchema(SchemaId,SchemaName,Tenant)
 values('123','Car','A'),
 ('xyz','Car','All'),
 ('456','Truck','B'),
 ('abc','Jeep','A'),
 ('664','Car','B');
10 rows affected

----Correct result if Supplying Tenant as 'A'
select * 
from (
         select fs1.schemaid, fs1.schemaName, ar1.roleId, ar1.roleName, ar1.tenant ,
                rn = row_number() over (partition by fs1.schemaName
                           order by case when ar1.tenant = 'ALL' then 2 else 1 end, ar1.tenant)
         from   RolesTable ar1
         full outer join FormSchema fs1
                      on ar1.SchemaId= fs1.SchemaId
     ) as t3
where rn = 1
and  Tenant in ('A', 'All')

-----Wrong Result If supply Tenant as 'B'

select * 
from (
         select fs1.schemaid, fs1.schemaName, ar1.roleId, ar1.roleName, ar1.tenant ,
                rn = row_number() over (partition by fs1.schemaName
                           order by case when ar1.tenant = 'ALL' then 2 else 1 end, ar1.tenant)
         from   RolesTable ar1
         full outer join FormSchema fs1
                      on ar1.SchemaId= fs1.SchemaId
     ) as t3
where rn = 1
and  Tenant in ('B', 'All');





schemaid
schemaName
roleId
roleName
tenant
rn


123
Car
40ecca83-7fd9-4d63-9f56-c7a48442d844
#Test-1
A
1

abc
Jeep
40ecca83-7fd9-4d63-9f56-c7a48442d844
#Test-1
A
1




schemaid
schemaName
roleId
roleName
tenant
rn


456
Truck
40ecca83-7fd9-4d63-9f56-c7a48442d844
#Test-1
B
1

schemaid	schemaName	roleId	roleName	tenant	rn
123	Car	40ecca83-7fd9-4d63-9f56-c7a48442d844	#Test-1	A	1
abc	Jeep	40ecca83-7fd9-4d63-9f56-c7a48442d844	#Test-1	A	1