Selecting by permissions table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Selecting by permissions table

Author

Topic

yariv
Starting Member

2 Posts

Posted - 2002-03-19 : 08:21:12

I have a simple query that needs to be complicated to suit permissions. This is the simple query:


select 	INNERCODE, Description 
from 	TypTbl

Now, I have another permissions table that may or may not specify allowed TypTbl values for each user. I tried changing the above query to:

 
select 	INNERCODE, Description 
from 	TypTbl
where	(exists (select User_InnerCode from TblUser where User_UserCode = 1) and INNERCODE in (select User_TableCode from TblUser where User_TableType = 'T' and User_UserCode = 1))

This was supposed to retrieve all values if the exists sub-query returns false and retrieve specific values if it returns true. However, it doesn't work. It does retrieve specific values if there is a permission defined, but it does not retrieve anything if there is no permission defined.
What am I doing wrong and how do I repair it?
TIA, Yariv

yariv
Starting Member

2 Posts

Posted - 2002-03-19 : 08:31:54

Ok, it was solved. Instead of EXISTS AND I will use NOT EXISTS OR:

 
select 	INNERCODE, 
	Description 
from 	TypTbl
where	(not exists (select User_InnerCode from TblUser where User_UserCode = 1) or INNERCODE in (select User_TableCode from TblUser where User_TableType = 'T' and User_UserCode = 1))

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources