Query Help - 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 2005 Forums

Transact-SQL (2005)

Query Help

Author

Topic

Fishwagon
Starting Member

10 Posts

Posted - 2011-03-14 : 14:30:54

hello all,

This query works fine as is....

SELECT Users.UserID, Users.Name, Users.Description, Users.UserPwd, Users.ReadOnly, Users.Linked, Users.DomainID, Users.SID, Groups.Name AS Expr1, Groups.Description AS Expr2, Groups.Linked AS Expr3, Groups.ReadOnly AS Expr4, Groups.DomainID AS Expr5, Groups.SID AS Expr6,

Case Relationships.RightTableType
When 1 Then 'Rights'
When 2 Then 'Processes'
When 3 Then 'Batch Class'
When 4 Then 'Batch'
Else 'Unknown'
End As 'Rights Type',

Case (Relationships.RightTablePrimaryKey)
When '1' Then 'Admin Right'
When '2' Then 'Admin Util Right'
When '3' Then 'Edit Profiles Right'
When '4' Then 'Batch Editing Right'
When '5' Then 'User Tracking Right'
When '6' Then 'Report Viewer Right'
When '7' Then 'Batch Routing Right'
Else Relationships.RightTablePrimaryKey
End As 'Right'

FROM Relationships

INNER JOIN Groups ON Relationships.ProfilePrimaryKey = Groups.ProfilePrimaryKey

INNER JOIN RightTableTypes ON Relationships.RightTableType = RightTableTypes.RightTableType

INNER JOIN User_Group ON Groups.ProfilePrimaryKey = User_Group.GroupProfilePrimaryKey

INNER JOIN Users ON User_Group.UserProfilePrimaryKey = Users.ProfilePrimaryKey

CROSS JOIN Processes
CROSS JOIN BatchDefID
CROSS JOIN Rights

Now, what I REALLY need is that the 2nd Case Statement needs to react to the first case statement.

in other words, the selections in the 2nd case statement are only valid if the Relationships.RightTableType is 1 (or 'Rights') I need different criteria if Relationships.RightTableType is 2, 3, 4 or 'Unknown'

It's as if I have to do "IF Relationships.RightTableType = 1 Then do this case statement for Relationships.RightTablePrimaryKey", if it's 2 then do this case statement, etc..

Any help would be appreciated.

Rich

Thanks and have a great day...

Rich

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2011-03-14 : 14:59:46

1 Way:
SELECT Users.UserID, Users.Name, Users.Description, Users.UserPwd, Users.ReadOnly, Users.Linked, Users.DomainID, Users.SID, Groups.Name AS Expr1, Groups.Description AS Expr2, Groups.Linked AS Expr3, Groups.ReadOnly AS Expr4, Groups.DomainID AS Expr5, Groups.SID AS Expr6,

Case Relationships.RightTableType
When 1 Then 'Rights'
When 2 Then 'Processes'
When 3 Then 'Batch Class'
When 4 Then 'Batch'
Else 'Unknown'
End As 'Rights Type',

Case Relationships.RightTableType -- (Relationships.RightTablePrimaryKey)
When 1 Then
Case Relationships.RightTablePrimaryKey
When '1' Then 'Admin Right'
When '2' Then 'Admin Util Right'
When '3' Then 'Edit Profiles Right'
When '4' Then 'Batch Editing Right'
When '5' Then 'User Tracking Right'
When '6' Then 'Report Viewer Right'
When '7' Then 'Batch Routing Right'
Else Relationships.RightTablePrimaryKey
End
When 2 Then
Case Relationships.RightTablePrimaryKey
When '1' Then 'XYZ'
When '2' Then 'ABC'
When '3' Then 'EDF'
When '4' Then 'GHI'
Else 'XXX'
End
END As 'Right'
FROM Relationships

Fishwagon
Starting Member

10 Posts

Posted - 2011-03-14 : 15:18:12

Thank you sir!! I figured there was a way to do it but I was doing it wrong. Thanks for your example, works great....

Thanks and have a great day...

Rich

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2011-03-15 : 16:11:17

quote:
Originally posted by Fishwagon

Thank you sir!! I figured there was a way to do it but I was doing it wrong. Thanks for your example, works great....

Thanks and have a great day...

Rich

You are welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources