Conditional T-SQL - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Conditional T-SQL

Author

Topic

sqlpal2007
Posting Yak Master

200 Posts

Posted - 2011-07-28 : 20:58:03

Hello All,

I have to write a select based on user permission table.

If user is has access to the customer then will be added to his list in UserPermissions table. If not then user shouldn't see that customer record in the result. One exception is user can be administrator which is defined in another table. If he/she is admin then should see all the customers and that is defined in UserAdminLocations tbale. If the customer matches in UserPermissions and UserAdminLocations for a user then that user shoudl see all the records. @UserID, @CustoemrID are parameters-

Thanks,

Here are the tables-


Customers-
CustID    Customerid      Locaddr     Locdetails
001       C001            test1       testd1
002       C023            test1       testd1
003       C005            test1       testd1
004       C520            test1       testd1
005       C528            test1       testd1

UserPermissions-
UserID    CustomerID     
1         C001
2         C520

UserAdminLocations-
CustomerID
C520
C022

Output - For UserID = 1
001       C001            test1       testd1

For UserID = 2 - he is admin
001       C001            test1       testd1
002       C023            test1       testd1
003       C005            test1       testd1
004       C520            test1       testd1
005       C520            test1       testd1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-29 : 01:46:35

why is userid not included in UserAdminLocations? how will you know which user has admin priviledges without userid in it?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-29 : 01:54:55

anyways as per data model now. the query should be like


SELECT c.*
FROM Customers c
LEFT JOIN UserPermissions u
ON c.CustomerID = u.CustomerID
LEFT JOIN UserAdminLocations ual
ON ual.CustomerID = c.CustomerID
WHERE u.UserID = @UserID
OR ual.CustomerID IS NOT NULL

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources