| Author |
 Topic  |
|
|
atulkukreja
Starting Member
13 Posts |
 Posted - 01/11/2012 : 09:02:04
|
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
16769 Posts |
Posted - 01/11/2012 : 09:07:42
|
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
|
 |
|
|
atulkukreja
Starting Member
13 Posts |
Posted - 01/11/2012 : 12:01:51
|
My apologies, I had the wrong DDL for one table, it has been updated.
|
Edited by - atulkukreja on 01/11/2012 12:04:10 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48076 Posts |
Posted - 01/11/2012 : 12:40:22
|
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/
|
|
|
|
atulkukreja
Starting Member
13 Posts |
Posted - 01/11/2012 : 12:52:09
|
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 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48076 Posts |
Posted - 01/11/2012 : 12:53:52
|
yep even that would do for current data set
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
|
|
Logical XOR With A Character Value
