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.
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
> 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
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
tablenorecords
SELECT a.,
b.
FROM tableok a
LEFT JOIN tablenorecords b
ON a.userid = b.userid
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.