Query help for values for multiple values from subquery

1

hi,

This is security table and given user name needs to records based region and accesslevel from the fact table

user------region-----accesslevel
ABC------Asia--------Full
ABC------EMEA-------Restricted
XYZ------AMERICAS----Full
XYZ------EMEA----------Full
PQR------ASIA-------Restricted
PQR------EMEA------Full

select * from Fact_table A inner join
( select region,accesslevel from security where user = 'ABC') B
on A.region = B.region and A.access = B.accesslevel

there is chance to multiple records for one user. Will this query be fast or need any changes as the fact table have 45k records.

Thanks.

2

Indexes are important for speed, so you need to coonsider having an index on region and accesslevel field of the fact_table.

If this is your expected result

user  region    accesslevel
 ABC  Asia      Full
 ABC  EMEA      Restricted
 XYZ  AMERICAS  Full
 XYZ  EMEA      Full
 PQR  ASIA      Restricted
 PQR  EMEA      Full

then (given that all result fields are in security table) you could do

select a.user
      ,a.region
      ,a.accesslevel
  from security as a
 where a.user='ABC'
   and exists(select 1
                from fact_table as b
               where b.region=a.region
                 and b.access=a.accesslevel
             )
;
3

Hi,

Thanks for help. But I need data from the Fact table based on the security restriction

So not sure how your query will return Fact table records.

select * from Fact_table A
inner join ( select region,accesslevel from Security where BOUser = 'ABC') B
ON A.region = B.region and A.access IN ( Case
When B.accesslevel ='Full' THEN ('Restricted','Unrestricted','Undefined')
When B.accesslevel ='Limited' THEN ('Unrestricted')
END)

Also I got more additional filter that when security.accesslevel is Full then pull these 'Restricted','Unrestricted','Undefined' access type from Fact table

when security.accesslevel is Limited then pull these 'Unrestricted' access type from Fact table

when giving multiple value in then part is giving error. what way is can be resolved.

Thanks.

4

Why? As I read your post, you want to show fields only from security table.

and since you're using inner join, you indicate you want intersecting rows.

To add your additional criterias, try this:

select a.user
      ,a.region
      ,a.accesslevel
  from security as a
 where a.bouser='ABC'
   and a.access in ('Restricted','Unrestricted','Undefined')
   and exists(select 1
                from fact_table as b
               where b.region=a.region
                 and b.access=a.accesslevel
                 and ((b.accesslevel='Full'
                 and   a.access in ('Restricted','Unrestricted','Undefined')
                      )
                  or  (b.accesslevel='Limited'
                 and   a.access='Unrestricted'
                     ))
             )
;
5

thanks for your suggestion. I changed my query like below and it is working

select * from Fact_table A
inner join ( select region,accesslevel from Security where BOUser = 'ABC') B
ON A.region = B.region and ( (
B.accesslevel ='Full' AND A.access IN ('Restricted','Unrestricted','Undefined'))
OR (B.accesslevel ='Limited' AND A.access IN ('Unrestricted'))
)