Mergeing Records based on priority in a single query

[ItsSree]

Hi All,

Well the problem in short is merging rows based on some priority .


My First Table Structure is Name is User_Security
User_Name varchar
User_Group Varchar



My Second Table Structure is Name is Screen_Security

User_Group Varchar
Screen_id varchar
Read/Write integer value either 1/0
Read integer value either 1/0
Disable integer value either 2/0




Now the actual problem is

A User may be in different groups and suppose X user is in Y and Z group then when i select my query based on user name from second table i get No of Screens * No of groups a user belongs
instead i want to merge the records which has identical screen ids
based on priority like this
Read/Write > Read > Disable

if there is Read/Write right for any group the user belongs then for that particular screen that should be his rights irrespective of any other rights and if he has Read and Disable but no Read/Write then Read should be returned else it should be Disable.

Another point to note is if there is a read/write right then that field is marked 1 else 0 same for read but for disable it is 2 if present else 0.


Can Any one suggest how to get this in single query

Thanks
ItsSree

 

[kutty007]

I am not sure if I have understood your requirement correctly.

Yet, this is what I have comeup with.

Hope you find it useful

Regards

Karthik


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[USR]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[USR]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[USRGRP]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[USRGRP]
GO

CREATE TABLE USR (User_Name varchar(20), User_Group Varchar(20))
GO
CREATE TABLE USRGRP ( User_Group Varchar (20),
Screen_id varchar (20),
[Read/Write] integer,
[Read] integer,
[Disable] integer
)

GO
INSERT INTO USR VALUES ('PRADEEP','MASTER')
INSERT INTO USR VALUES ('KRISHNA','TRAN')
INSERT INTO USR VALUES ('KUMAR','MASTER')
INSERT INTO USR VALUES ('PRAKASH','MASTER')
INSERT INTO USR VALUES ('KAMAL','TRAN')
INSERT INTO USR VALUES ('NEELA','MASTER')
INSERT INTO USR VALUES ('KANNAN','TRAN')


INSERT INTO USRGRP VALUES ('MASTER','1',1,1,0)
INSERT INTO USRGRP VALUES ('TRAN','2',0,1,0)
INSERT INTO USRGRP VALUES ('MASTER','3',0,1,0)
INSERT INTO USRGRP VALUES ('MASTER','4',0,1,1)
INSERT INTO USRGRP VALUES ('TRAN','5',0,0,1)
INSERT INTO USRGRP VALUES ('MASTER','6',1,1,1)
INSERT INTO USRGRP VALUES ('TRAN','7',0,1,0)
INSERT INTO USRGRP VALUES ('MASTER','8',0,0,1)
GO



SELECT [User_Name], B.User_Group, B.Screen_Id,
CASE [Read/Write] WHEN 1 THEN '[READ/WRITE]'
ELSE CASE [Read]
WHEN 1 THEN 'READ'
ELSE
CASE [DISABLE]
WHEN 1 THEN 'DISABLED'
END
END
END as [Access]

FROM USR A, USRGRP B
WHERE A.User_Group = B.User_Group
ORDER BY [User_Name]