-1

I'm trying to write a WHERE clause, which returns all data if the column is empty. I have a csv file (static_table) which consists of:

enter image description here

If the permission is empty that means that the user will need to see everything. So if the hvfg23 user is logged in and runs a query that has country, region and brand, whenever the query is executed the user can see data for every country, for Europe region and all brands.

The query I have at the moment looks something like this:

select * from t1
where(select restriction, type from t2 where user_id = {{current_userId()}} )

I'm having trouble adjusting the query to what exactly I need, the help will be very appreciated.

Teddy
  • 9
  • 1
  • 9
  • 1
    Questions such as this would benefit from a [Minimal, Reproducible Example](https://stackoverflow.com/help/minimal-reproducible-example) – Stu Oct 28 '21 at 09:22
  • Note that in SQL, tables have _columns_, not fields. – jarlh Oct 28 '21 at 09:23

1 Answers1

0

If I well understand the structure of your tables so this will select permissions of the current user then select its related info in case it s not null else select column which means no condition on this column

declare @country nvarchar(50), @region nvarchar(50), @brand nvarchar(50)

set @country=(Select  [t2].[permission] 
    from [t2] where [t2].[type]='country' 
    and [t2].[user_id] = {{current_userId()}})

set @brand=(Select  [t2].[permission] 
    from [t2] where [t2].[type]='brand' 
    and [t2].[user_id] =  {{current_userId()}})

set @region=(Select  [t2].[permission] 
    from [t2] where [t2].[type]='region'
    and [t2].[user_id] =  {{current_userId()}})

select * from [t1] where
[t1].[country]= case when @country IS NULL
    then [t1].[country]  else @country end 
and [t1].[brand]= case when @brand IS NULL 
    then [t1].[brand]  else @brand end
and [t1].[region]= case when @region IS NULL
    then [t1].[region]  else @region end
Halima Ghannoum
  • 3
  • 3
  • 1
    Please consider including a brief explanation of [how and why this solves the problem](https://meta.stackoverflow.com/q/392712/13138364). This will help future readers to better understand your solution. – tdy Oct 28 '21 at 13:40