SQL Server Forums
Profile | Register | 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
 New Topic  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
17655 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
52325 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
52325 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  
 New 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.09 seconds. Powered By: Snitz Forums 2000