clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2043017 fiddles created (16655 in the last week).

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))
 hidden batch(es)


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
 hidden batch(es)


----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
 hidden batch(es)