Logical XOR With A Character Value

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

Transact-SQL (2000)

Logical XOR With A Character Value

Author

Topic

atulkukreja
Starting Member

13 Posts

Posted - 2012-01-11 : 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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-01-11 : 09:07:42

[code]
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]
[/code]

KH
[spoiler]Time is always against us[/spoiler]

atulkukreja
Starting Member

13 Posts

Posted - 2012-01-11 : 12:01:51

My apologies, I had the wrong DDL for one table, it has been updated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-11 : 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 - 2012-01-11 : 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

52326 Posts

Posted - 2012-01-11 : 12:53:52

yep even that would do for current data set

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources