-2

I have two table Name as 'ApplicationRoles' and 'FormSchema'.

I have to select result based on passing Tenant column like -'A' or 'All' -'B' or 'All' -'C' or 'All' and so on.

Now i have to get result by joining both table based on below condition.And in query Tenant 'All' is default and it is common between other tenant.

Condition: let suppose in query if i am passing Tenant as 'A' then in result all rows belong to Tenant 'A' as well as Tenant 'All' but not Tenant 'All' one row which is already there in Tenant 'A'.

please see the fiddle for table and record. for example dbfiddle I am supplying Tenant as 'A'.then below row should not come in result because SchemaName Car already came in result through Tenant 'A'.

55b7f5e3-b8bd-4857-8db6-11d0eae92194 Car 40ecca83-7fd9-4d63-9f56-c7a48442d844 #Test-1 All

Tried query:

    select * from (select fs1.schemaid,fs1.schemaName,ar1.roleId,ar1.roleName,ar1.tenant from
    
    (select ar.SchemaId,ar.Tenant,ar.RoleId,ar.RoleName,ar.Capability 
    from ApplicationRoles ar) ar1
    full outer join
    (select fs.SchemaId,fs.SchemaName,fs.Tenant,fs.Capability from FormSchema fs) fs1
    
    on ar1.SchemaId= fs1.SchemaId) as t3
where (t3.Tenant='All' or t3.tenant='A')

Expected/Required Result:output

How to get Result if SchemaName is already there then not select 'All' Tenant row.

cj devin
  • 1,045
  • 3
  • 13
  • 48
  • you need to show us the expected result – Squirrel Jun 19 '21 at 07:54
  • @Squirrel updated question with expected/required result – cj devin Jun 19 '21 at 10:12
  • I'm lost. Both tables have a `tenant` column, so it is unclear what you want. You are also using a `full join` when an `inner join` would suffice, so please explain why you think a `full join` is appropriate. – Gordon Linoff Jun 19 '21 at 11:10
  • @GordonLinoff Expected output using query i want that is : In result all rows will be there which all belongs to Tenant 'A' as well as Tenant 'All' but if Tenant 'A' and Tenant 'All' exists with same SchemaName then get 'A' Tenant row not 'All' Tenant row for the SchemaName.In other word in result take all row which all belong to 'A' and 'All' but same SchemaName case it should choose Tenant 'A' row not Tenant 'All' row. – cj devin Jun 19 '21 at 11:36
  • Why i used full outer join might be three case 1).May be some schemaName exist in Tenant 'A' only...take in result 2).May be some schemaName exist in Tenant 'All' only....take in result 3).And may be some same schemaName exist in Tenant 'A' and 'All ' both.......only take 'A' – cj devin Jun 19 '21 at 11:37

1 Answers1

1

use row_number() with order by to identify the required row

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   ApplicationRoles ar1
         full outer join FormSchema fs1
                      on ar1.SchemaId= fs1.SchemaId
     ) as t3
where rn = 1
and  Tenant in ('A', 'All')
Squirrel
  • 23,507
  • 4
  • 34
  • 32