January 5, 2012 at 1:22 am
I have four table with data below
1)
CREATE TABLE role(
[roleID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_role_roleID] DEFAULT (newid()),
[applicationname] [nvarchar](256) ,
[rolename] [nvarchar](256))
its data is below//roleId supposed to be int
1 myapp admin
2 myapp powerUser
...................................
2nd table)
CREATE TABLE UsersInRole(
[userid] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[rolename] [nvarchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[applicationname] [nvarchar](256) )
Its data are give below.userid is supposed to be A,B,C etc
A admin myapp
B admin myapp
C Poweruser myapp
....................................................
3rd table)
tbl_be_Modules(Id,ParentId,DisplayName,url,IsActive)
And its data is given here
Id ParentId DisplayName url IsActive
1 0 Company 1
2 0 POI 1
3 1 CompanyList 'cmp.aspx' 1
4 2 POIListLevelOne 'P1.aspx' 1
5 2 POIListLevelTwo 'P2.aspx' 1
...........................................................................
4th table)
rolesPermissions(Id,roleId,moduleId,canRead,canWrite,canDelete)
And its data is below
Id roleId moduleId canRead canWrite canDelete
1 1 3 1 1 1
2 1 4 1 1 1
3 2 5 1 0 0
-----------------------------------------------------
Now Finally i want to show Record Like this
ID RoleName DisplayName/Modules canRead canWrite canDelete
Id admin CompanyList 1 1 1
Id admin POIListLevelOne 1 1 1
Id PowerUser POIListLevelTwo 1 0 0
---------------------------
mean i want to show all active,child data....Id may be any one in above case.
Please give me a concise select Query?
Regards
January 7, 2012 at 11:30 pm
I tried like this
select R.rolename,P.canRead,P.canWrite,P.canDelete,M.Id,M.DisplayName from role R,rolesPermissions P,tbl_be_Modules M where M.Id=P.ModuleId and R.roleID=P.roleId
January 9, 2012 at 6:01 am
The tables as you've laid them out are badly designed and incorrect.
I've corrected the issue, but not the bad design : -
CREATE TABLE role(
[roleID] INT IDENTITY,
[applicationname] [nvarchar](256) ,
[rolename] [nvarchar](256))
INSERT INTO role ([applicationname], [rolename])
SELECT 'myapp', 'admin'
UNION ALL SELECT 'myapp', 'powerUser'
CREATE TABLE UsersInRole(
[userid] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[rolename] [nvarchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[applicationname] [nvarchar](256) )
INSERT INTO UsersInRole
SELECT 'A', 'admin', 'myapp'
UNION ALL SELECT 'B', 'admin', 'myapp'
UNION ALL SELECT 'C', 'Poweruser', 'myapp'
CREATE TABLE tbl_be_Modules(Id INT IDENTITY, ParentId INT,DisplayName VARCHAR(100),
url VARCHAR(50), IsActive BIT)
INSERT INTO tbl_be_Modules
SELECT 0, 'Company', NULL, 1
UNION ALL SELECT 0, 'POI', NULL, 1
UNION ALL SELECT 1, 'CompanyList', 'cmp.aspx', 1
UNION ALL SELECT 2, 'POIListLevelOne', 'P1.aspx', 1
UNION ALL SELECT 2, 'POIListLevelTwo', 'P2.aspx', 1
CREATE TABLE rolesPermissions(Id INT IDENTITY, roleId INT, moduleId INT, canRead BIT,
canWrite BIT,canDelete BIT)
INSERT INTO rolesPermissions
SELECT 1, 3, 1, 1, 1
UNION ALL SELECT 1, 4, 1, 1, 1
UNION ALL SELECT 2, 5, 1, 0, 0
So, on to the answer.
SELECT R.roleID, R.rolename, P.DisplayName, P.canRead, P.canWrite, P.canDelete
FROM role R
INNER JOIN (SELECT rp.roleId, rp.canDelete, rp.canRead, rp.canWrite, rp.moduleId,
tm.DisplayName, tm.IsActive, tm.ParentId, tm.url
FROM rolesPermissions rp
INNER JOIN tbl_be_Modules tm ON rp.moduleId = tm.Id) P ON R.roleID = P.roleId
Which returns -
roleID rolename DisplayName canRead canWrite canDelete
----------- ------------ ---------------------------------------------------------------------------------------------------- ------- -------- ---------
1 admin CompanyList 1 1 1
1 admin POIListLevelOne 1 1 1
2 powerUser POIListLevelTwo 1 0 0
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply