Reputation: 1375
I have two SQL Server tables:
FormSchema
columns
SchemaId, SchemaName, Tenant
RoleTable
columns
SchemaId, RoleId, RoleName, Tenant
Expected result (includes below three conditions):
Query:
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')
Please see the db fiddle for table, records, and expected and actual result.
The issue I am facing like while giving Tenant as 'B' expected result is wrong but instead if i use 'A' expected result is correct.
Expected result for 'B' is wrong** because it's not satisfying the following:
Expected Result for 'B':
SchemaId RoleId RoleName Tenant
--------------------------------------------------------------------
'664' '40ecca83-7fd9-4d63-9f56-c7a48442d844' '#Test-1' 'B'
'456' '40ecca83-7fd9-4d63-9f56-c7a48442d844' '#Test-1' 'B'
Thanks in advance.
Upvotes: 1
Views: 918
Reputation: 164234
You should move the condition for the Tenant
from the outer query inside the subquery:
select *
from (
select fs1.schemaid, fs1.schemaName, ar1.roleId, ar1.roleName, ar1.tenant,
row_number() over (
partition by fs1.schemaName
order by case when ar1.tenant = 'ALL' then 2 else 1 end, ar1.tenant
) rn
from RolesTable ar1 full outer join FormSchema fs1
on ar1.SchemaId= fs1.SchemaId
where ar1.Tenant in (?, 'All')
) as t3
where rn = 1
Replace ?
with 'A'
or 'B'
.
Also, it is not clear why you are doing a FULL
join instead of a LEFT
join.
If you want in the results unmatched rows from both tables, then may be you should use COALESCE()
:
where coalesce(ar1.Tenant, fs1.Tenant) in (?, 'All')
See the demo.
Upvotes: 1
Reputation: 1271241
I find the logic a bit hard to follow. And I don't understand why you are using a full join
when an inner join
does the work.
However, I think what you need to do is filter in the subquery:
select *
from (select fs1.schemaid, fs1.schemaName, ar1.roleId, ar1.roleName, ar1.tenant ,
row_number() over (partition by fs1.schemaName
order by case when ar1.tenant = 'ALL' then 2 else 1 end, ar1.tenant) as seqnum
from RolesTable ar1 join
FormSchema fs1
on ar1.SchemaId= fs1.SchemaId
where ar1.Tenant in ('B', 'All')
) t3
where seqnum = 1 ;
Upvotes: 1