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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Logical XOR With A Character Value
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

atulkukreja
Starting Member

13 Posts

Posted - 01/11/2012 :  09:02:04  Show Profile  Reply with Quote
Using SQL Server 2005 Express, table / data definition:

CREATE TABLE [dbo].[rolePrivileges] (
[role] varchar(50) NOT NULL,
[create] char(1) NOT NULL DEFAULT 'N',
[read] char(1) NOT NULL DEFAULT 'N',
[update] char(1) NOT NULL DEFAULT 'N',
[delete] char(1) NOT NULL DEFAULT 'N'
) ON [PRIMARY]

INSERT INTO [rolePrivileges] ([role],[create],[read],[update],[delete]) VALUES ('admin', 'Y', 'Y', 'N', 'N')
INSERT INTO [rolePrivileges] ([role],[create],[read],[update],[delete]) VALUES ('manager', 'N','Y','N','Y')
INSERT INTO [rolePrivileges] ([role],[create],[read],[update],[delete]) VALUES ('staff', 'N','Y','Y','N')

CREATE TABLE [dbo].[userRoles] (
[user] varchar(50) NOT NULL,
[role] varchar(50) NOT NULL
) ON [PRIMARY]

INSERT INTO [userRoles] ([user], [role]) VALUES ('jim', 'admin')
INSERT INTO [userRoles] ([user], [role]) VALUES ('jim', 'manager')

A user may be assigned multiple roles, and gets the highest privilege allowed by any role to which he is assigned.
I need to display a single row with user jim's privileges, based on the two roles he is assigned. So the result I need is:

user create read update delete
-----------------------------------
jim Y Y N Y

Logically, if any role has a privilege with a 'Y', it needs to be displayed as a 'Y', else it remains an 'N'

Any help in writing the SQL statement is greatly appreciated.

scptech

Edited by - atulkukreja on 01/11/2012 12:03:28

khtan
In (Som, Ni, Yak)

Singapore
17689 Posts

Posted - 01/11/2012 :  09:07:42  Show Profile  Reply with Quote

select	r.[user],
	max(case when p.privilege = 'create' then allow end) as [create],
	max(case when p.privilege = 'read'   then allow end) as [read],
	max(case when p.privilege = 'update' then allow end) as [update],
	max(case when p.privilege = 'delete' then allow end) as [delete]
from	userRoles r
	inner join rolePrivileges p	on	r.role	= p.role
group by r.[user]



KH
Time is always against us

Go to Top of Page

atulkukreja
Starting Member

13 Posts

Posted - 01/11/2012 :  12:01:51  Show Profile  Reply with Quote
My apologies, I had the wrong DDL for one table, it has been updated.

Edited by - atulkukreja on 01/11/2012 12:04:10
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 01/11/2012 :  12:40:22  Show Profile  Reply with Quote
as per modified DDL, i think you need below

select	r.[user],
	max(case when p.create = 'Y' then 'Y' else 'N' end) as [create],
	max(case when p.read = 'Y' then 'Y' else 'N' end) as [read],
	max(case when p.update = 'Y' then 'Y' else 'N' end) as [update],
	max(case when p.delete = 'Y' then 'Y' else 'N' end) as [delete]
from	userRoles r
	inner join rolePrivileges p	on	r.role	= p.role
group by r.[user]


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

Go to Top of Page

atulkukreja
Starting Member

13 Posts

Posted - 01/11/2012 :  12:52:09  Show Profile  Reply with Quote
visakh16 - thanks, that works.

Since the value for each privilege is either Y or N, is there any need for the case construct?

So can the SQL not be:

select r.[user], max(p.create) as [create], ......
from userRoles r
inner join rolePrivileges p on r.role = p.role
group by r.[user]




scptech
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 01/11/2012 :  12:53:52  Show Profile  Reply with Quote
yep even that would do for current data set

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

Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000