SQLTeam.com | Weblogs | Forums

If not exists pull all data


#1

Hi

I need help in pulling data based on security table, when the user is there in security table then pull data only for that region and security type. below query is working. But for super users all data needs to be pulled and there will not be any record in the security table. How to add superuser in the below query.

(select vendorNo from Vendor V
inner join ( select region,AccessLevel from UserSecurity where userID ='XXX') S
ON S.region = V.region
and ( (S.Accesslevel ='Full' AND V.vendorSecurity IN
('Restricted','Unrestricted','Undefined'))
OR (S.Accesslevel ='Limited' AND V.vendorSecurity IN ('Unrestricted'))
)

Thanks in advance.


#2

Maybe

select vendorNo from Vendor V
left join UserSecurity S ON userID ='XXX'
WHERE (S.region = V.region
and ( (S.Accesslevel ='Full' AND V.vendorSecurity IN
('Restricted','Unrestricted','Undefined'))
OR (S.Accesslevel ='Limited' AND V.vendorSecurity IN ('Unrestricted')) ) OR s.userID IS NULL


#3
> But for super users all data needs to be pulled 
> and there will not be any record in the security table.

i am going to give an illustriation

i hope i am able to explain what i am understanding
if i misunderstood please let me know ... thanks
:slight_smile:
:slight_smile:

example:
Left join has to be used on security table
select * from table a left join security b on a.column = b.column

drop create data
use tempdb 

go 


drop table tableok
go 

create table tableok
(
userid int null,
type varchar(10) null
)
go 

insert into tableok select 1 ,'user'
insert into tableok select 2 ,'user'
insert into tableok select 3 , 'super_user'
go 

select * from tableok
go 


drop table tablenorecords
go 

create table tablenorecords
(
userid int null,
address varchar(100) null 
)
go 

insert into tablenorecords select 1 , 'park avenue'
insert into tablenorecords select 2 , 'hyatt place'
insert into tablenorecords select 4 , 'telvas avenue'
go 

select * from tablenorecords
go

tableok
image

tablenorecords
image

SELECT a.,
b.

FROM tableok a
LEFT JOIN tablenorecords b
ON a.userid = b.userid

image


#4

thanks btrimpop, your query gave the required result. I did the same with exists and not exists. But sure your suggestion was similar.

harishgg1, thanks for your time. but I don't have userid is both the table.

really appreciated your help.