I have 2 tables that i have joined together to be able to evaluate the availabilty of rights to a user. the query is as below
SELECT R.RIGHT_ID,RIGHT_NAME,
RVIEW=CASE WHEN R.RIGHT_VIEW = 0 THEN NULL WHEN R.RIGHT_VIEW = 1 AND A.RIGHT_VIEW IS NULL THEN 0 ELSE A.RIGHT_VIEW END,
RADD=CASE WHEN R.RIGHT_ADD = 0 THEN NULL WHEN R.RIGHT_ADD = 1 AND A.RIGHT_ADD IS NULL THEN 0 ELSE A.RIGHT_ADD END,
REDIT=CASE WHEN R.RIGHT_EDIT = 0 THEN NULL WHEN R.RIGHT_EDIT = 1 AND A.RIGHT_EDIT IS NULL THEN 0 ELSE A.RIGHT_EDIT END,
RDEL=CASE WHEN R.RIGHT_DELETE = 0 THEN NULL WHEN R.RIGHT_DELETE = 1 AND A.RIGHT_DELETE IS NULL THEN 0 ELSE A.RIGHT_DELETE END,
R.RIGHT_TYPE, A.RIGHT_VALUE
FROM RIGHTS_MASTER R LEFT JOIN
(SELECT RIGHT_ID,RIGHT_ADD,RIGHT_EDIT,RIGHT_DELETE,RIGHT_VIEW,RIGHT_VALUE
FROM ASSIGNED_RIGHTS_MASTER INNER JOIN USER_MASTER ON USER_MASTER.USER_ID = ASSIGNED_RIGHTS_MASTER.USER_ID
WHERE USER_MASTER.USER_ID = '4') A
ON R.RIGHT_ID = A.RIGHT_ID
WHERE R.RIGHT_MODULE = 'CF'
ORDER BY RIGHT_NAME
now i want to display this in a Radgrid with selectable checkboxes for view,add,delete and edit columns. These should display the value that is in the table for each of the columns.
below is the structure of the 2 tables used.
USE [LODGERMS_MAIN]
GO
/****** Object: Table [dbo].[ASSIGNED_RIGHTS_MASTER] Script Date: 03/21/2013 12:23:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ASSIGNED_RIGHTS_MASTER](
[LODGE_ID] [nvarchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[USER_ID] [nvarchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[RIGHT_ID] [int] NOT NULL,
[RIGHT_VALUE] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[RIGHT_EDIT] [bit] NOT NULL,
[RIGHT_VIEW] [bit] NOT NULL,
[RIGHT_DELETE] [bit] NOT NULL,
[RIGHT_ADD] [bit] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ASSIGNED_RIGHTS_MASTER] WITH NOCHECK ADD CONSTRAINT [FK_ASSIGNED_RIGHTS_MASTER_PROPERTY_MASTER] FOREIGN KEY([LODGE_ID])
REFERENCES [dbo].[PROPERTY_MASTER] ([LODGE_ID])
GO
ALTER TABLE [dbo].[ASSIGNED_RIGHTS_MASTER] CHECK CONSTRAINT [FK_ASSIGNED_RIGHTS_MASTER_PROPERTY_MASTER]
USE [LODGERMS_MAIN]
GO
/****** Object: Table [dbo].[RIGHTS_MASTER] Script Date: 03/21/2013 12:25:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[RIGHTS_MASTER](
[RIGHT_ID] [int] IDENTITY(1,1) NOT NULL,
[RIGHT_NAME] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[RIGHT_TYPE] [nvarchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[RIGHT_VALUE] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[RIGHT_MODULE] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[RIGHT_EDIT] [bit] NOT NULL,
[RIGHT_VIEW] [bit] NOT NULL,
[RIGHT_DELETE] [bit] NOT NULL,
[RIGHT_ADD] [bit] NOT NULL,
CONSTRAINT [PK_RIGHTS_MASTER] PRIMARY KEY CLUSTERED
(
[RIGHT_NAME] ASC
) ON [PRIMARY]
) ON [PRIMARY]
Please assist me as i am stuck on a project.
