Complex Select - SQL Server Forums

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)

Complex Select

Author

Topic

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2005-05-18 : 18:41:27

Hello gals and guys.

I'm trying to create an administrative controlled menu based of user and group/role permissions. For example: User1 is in user group/role Role1. I have a table with users named User, another named Menu with a list of all the possible menu items and another named UserType listed all the user group/roles. Between them I have a table linking them together UserMenu and UserTypes, since one user can have multiple roles and of course any user can have more than one menu item. Here's some code to build the tables:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_UserMenu_Menu]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[UserMenu] DROP CONSTRAINT FK_UserMenu_Menu
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_UserTypes_User]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[UserTypes] DROP CONSTRAINT FK_UserTypes_User
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_UserMenu_UserPageType]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[UserMenu] DROP CONSTRAINT FK_UserMenu_UserPageType
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_UserTypes_UserType]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[UserTypes] DROP CONSTRAINT FK_UserTypes_UserType
GO

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

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

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

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

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

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

CREATE TABLE [dbo].[Menu] (
	[MenuID] [int] NOT NULL ,
	[Menu] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[MenuLink] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[MenuDescription] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[User] (
	[UserID] [int] NOT NULL ,
	[UserName] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[UserPassword] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[UserMenu] (
	[UserMenuID] [int] NOT NULL ,
	[UserPageTypeID] [int] NOT NULL ,
	[UserID] [int] NOT NULL ,
	[MenuID] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[UserPageType] (
	[UserPageTypeID] [int] NOT NULL ,
	[UserPageType] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[UserType] (
	[UserTypeID] [int] NOT NULL ,
	[UserType] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[UserTypes] (
	[UserTypesID] [int] NOT NULL ,
	[UserID] [int] NOT NULL ,
	[UserTypeID] [int] NOT NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Menu] WITH NOCHECK ADD 
	CONSTRAINT [PK_Menu] PRIMARY KEY  CLUSTERED 
	(
		[MenuID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[User] WITH NOCHECK ADD 
	CONSTRAINT [PK_User] PRIMARY KEY  CLUSTERED 
	(
		[UserID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[UserMenu] WITH NOCHECK ADD 
	CONSTRAINT [PK_UserMenu] PRIMARY KEY  CLUSTERED 
	(
		[UserMenuID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[UserPageType] WITH NOCHECK ADD 
	CONSTRAINT [PK_UserPageType] PRIMARY KEY  CLUSTERED 
	(
		[UserPageTypeID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[UserType] WITH NOCHECK ADD 
	CONSTRAINT [PK_UserType] PRIMARY KEY  CLUSTERED 
	(
		[UserTypeID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[UserTypes] WITH NOCHECK ADD 
	CONSTRAINT [PK_UserTypes] PRIMARY KEY  CLUSTERED 
	(
		[UserTypesID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[User] ADD 
	CONSTRAINT [IX_UserName] UNIQUE  NONCLUSTERED 
	(
		[UserName]
	)  ON [PRIMARY] 
GO

 CREATE  INDEX [IX_UserID] ON [dbo].[UserTypes]([UserID]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_UserTypeID] ON [dbo].[UserTypes]([UserTypeID]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[UserMenu] ADD 
	CONSTRAINT [FK_UserMenu_Menu] FOREIGN KEY 
	(
		[MenuID]
	) REFERENCES [dbo].[Menu] (
		[MenuID]
	) ON DELETE CASCADE  ON UPDATE CASCADE ,
	CONSTRAINT [FK_UserMenu_UserPageType] FOREIGN KEY 
	(
		[UserPageTypeID]
	) REFERENCES [dbo].[UserPageType] (
		[UserPageTypeID]
	) ON DELETE CASCADE  ON UPDATE CASCADE 
GO

ALTER TABLE [dbo].[UserTypes] ADD 
	CONSTRAINT [FK_UserTypes_User] FOREIGN KEY 
	(
		[UserID]
	) REFERENCES [dbo].[User] (
		[UserID]
	) ON DELETE CASCADE  ON UPDATE CASCADE ,
	CONSTRAINT [FK_UserTypes_UserType] FOREIGN KEY 
	(
		[UserTypeID]
	) REFERENCES [dbo].[UserType] (
		[UserTypeID]
	) ON UPDATE CASCADE 
GO

ok now here's some data for those tables:

delete from [user]
delete from usertype
delete from usertypes
delete from usermenu
delete from userpagetype
delete from menu

insert into [user](userid,username,userpassword) values(1,'user1','123456')
insert into [user](userid,username,userpassword) values(2,'user2','654321')
insert into [user](userid,username,userpassword) values(3,'user3','456123')

insert into usertype(usertypeid,usertype) values(1,'admin')
insert into usertype(usertypeid,usertype) values(2,'finance')

insert into usertypes(usertypesid,userid,usertypeid) values(1,1,1) -- User1 is in admin group
insert into usertypes(usertypesid,userid,usertypeid) values(2,2,2) -- User2 is in finance group
insert into usertypes(usertypesid,userid,usertypeid) values(3,3,1) -- User3 is in admin group
insert into usertypes(usertypesid,userid,usertypeid) values(4,3,2) -- User3 is in finance group

insert into menu(menuid,menu,menulink,menudescription) values(1,'Home','index.asp','Go home')
insert into menu(menuid,menu,menulink,menudescription) values(2,'View Users','users.asp','View all registered users')
insert into menu(menuid,menu,menulink,menudescription) values(3,'View Financial Reports','finance.asp','View latest finance reports')

insert into userpagetype(userpagetypeid,userpagetype) values(1,'user')
insert into userpagetype(userpagetypeid,userpagetype) values(2,'usertype')

insert into usermenu(usermenuid,userpagetypeid,userid,menuid) values (1,2,1,1)
insert into usermenu(usermenuid,userpagetypeid,userid,menuid) values (2,2,1,2)
insert into usermenu(usermenuid,userpagetypeid,userid,menuid) values (3,2,2,1)
insert into usermenu(usermenuid,userpagetypeid,userid,menuid) values (4,2,2,3)
-- at this point 'admin' group/role have access to 'Home' and 'View Users'
-- while the 'finance' group/role have access to 'Home' and 'View Financial Reports' but not 'View Users'

-- Digested:
-- 	1 = record id
--	2 = userpagetypeid 2 is usertype...
--	1 = ...the above line tells us this record belongs to usertypeid in the 'usertype' table, which is 'admin'
--	1 = menu id 1, which is 'Home'

-- 	2 = record id
--	2 = userpagetypeid 2 is usertype...
--	1 = ...the above line tells us this record belongs to usertypeid in the 'usertype' table, which is 'admin'
--	2 = menu id 2, which is 'View Users'

-- 	3 = record id
--	2 = userpagetypeid 2 is usertype...
--	2 = ...the above line tells us this record belongs to usertypeid in the 'usertype' table, which is 'finance'
--	1 = menu id 1, which is 'Home'

-- 	4 = record id
--	2 = userpagetypeid 2 is usertype...
--	2 = ...the above line tells us this record belongs to usertypeid in the 'usertype' table, which is 'finance'
--	3 = menu id 3, which is 'View Financial Reports'

insert into usermenu(usermenuid,userpagetypeid,userid,menuid) values (5,1,1,3)
-- this adds the ability for 'User1' to have extra access to the 'View Financial Reports'

-- Digested:
-- 	5 = record id
--	1 = userpagetypeid 1 is user...
--	1 = ...the above line tells us this record belongs to userid in the 'user' table, which is 'user1'
--	3 = menu id 3, which is 'View Financial Reports'

Now I want to pass the userid to a stored procedure that returns the menu for that user and these are my desired results (based on userid):

If I pass a userid of:

userid = 1
result:(menu,menulink,menudescription)
'Home','index.asp','Go home'
'View Users','users.asp','View all registered users'
'View Financial Reports','finance.asp','View latest finance reports'
-- user1's results due to being part of the admin group/role and having an extra record because
-- of the last record in the 'usermenu' table

userid = 2
result:(menu,menulink,menudescription)
'Home','index.asp','Go home'
'View Financial Reports','finance.asp','View latest finance reports'
-- user2's results due to being part of the finance group/role

userid = 3
result:(menu,menulink,menudescription)
'Home','index.asp','Go home'
'View Users','users.asp','View all registered users'
-- user3's results due to being part of the admin group/role

I hope I was helpful in explaining this and hope someone can help me.

- RoLY roLLs

raclede
Posting Yak Master

180 Posts

Posted - 2005-05-19 : 03:13:35

CREATE PROCEDURE MenuSelProc
(
@userID int
)

AS
CREATE TABLE #temp ([id] [int] IDENTITY (1, 1) NOT NULL , menuID INT)
INSERT into #temp
---------------------------- END START TEMP TABLE ----------------------
------ 1) Selects All the menu filtered by @userid
SELECT
-- return the first non-null menu found
COALESCE
(UserMenu.MenuID, -- if null then search by UserID then by userPageType ID
(SELECT TOP 1 UserMenu.MenuID
FROM [USER] INNER JOIN
UserTypes ON [USER].UserID = UserTypes.UserID INNER JOIN
UserPageType ON UserTypes.UserTypeID = UserPageType.UserPageTypeID INNER JOIN
UserMenu ON UserPageType.UserPageTypeID = UserMenu.UserPageTypeID
WHERE ([USER].UserID = b.UserID) AND (UserTypes.UserTypeID = c.UserTypeID))) MenuID
FROM Menu INNER JOIN
UserMenu ON Menu.MenuID = UserMenu.MenuID RIGHT OUTER JOIN
[User] b ON UserMenu.UserID = b.UserID LEFT OUTER JOIN
UserTypes c ON b.UserID = c.UserID
WHERE (b.UserID = @userID)
---------------------------- END FILL TEMP TABLE ----------------------
---- 2) link all menuID in temp to menuID in menutable
SELECT Menu.Menu, Menu.MenuLink, Menu.MenuDescription
FROM Menu INNER JOIN
#temp ON #temp.MenuID = Menu.MenuID
DROP TABLE #temp
GO

Cursors are for those who doesn't know how to use SQL

raclede™

raclede
Posting Yak Master

180 Posts

Posted - 2005-05-19 : 03:15:47

--- to execute
exec menuselproc 1
--- result
Menu Menulink Desc
Home index.asp Go home
View Users users.asp View all registered users
View Financial Reports finance.asp View latest finance reports

Cursors are for those who doesn't know how to use SQL

raclede™

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2005-05-19 : 14:33:09

Works great! Thanks. Seems like a mess to me, but maybe because I'm so tired. :) Anyhow, I got one problem with this, which I'll try to fix, but seeing as I am worn out for the week, maybe you can help. If we add the following record:

insert into usermenu(usermenuid,userpagetypeid,userid,menuid) values (6,1,1,1)

This creates a duplicate record of HOME for user 1. I'm sure by adding select distinct it will work. But I post this to thank you and just in case it doesn't work, it's already here :)

Have a great one!

- RoLY roLLs

raclede
Posting Yak Master

180 Posts

Posted - 2005-05-19 : 21:11:28

just add distinct after the first select command

Cursors are for those who doesn't know how to use SQL

raclede™

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2005-05-23 : 10:27:36

Hello...after some testing there seems to be a problem with the above statement. Here's the problem. Add the following user and assign him to the finance department:

insert into [user](userid,username,userpassword) values(4,'user4','321654')
insert into usertypes(usertypesid,userid,usertypeid) values(5,4,2) -- User4 is in finance group

running the above sproc returns the menu HOME only, when it should return Home AND View Financial Reports.

Thanks.

- RoLY roLLs

raclede
Posting Yak Master

180 Posts

Posted - 2005-05-23 : 20:49:21

maybe you forgot this one

insert into usermenu(usermenuid,userpagetypeid,userid,menuid) values (4,2,4,3)

-- at this point 'admin' group/role have access to 'Home' and 'View Users'
-- while the 'finance' group/role have access to 'Home' and 'View Financial Reports' but not 'View Users'

-- Digested:
-- 1 = record id
-- 2 = userpagetypeid 2 is usertype...
-- 1 = ...the above line tells us this record belongs to usertypeid in the 'usertype' table, which is 'admin'
-- 1 = menu id 1, which is 'Home'

-- 2 = record id
-- 2 = userpagetypeid 2 is usertype...
-- 1 = ...the above line tells us this record belongs to usertypeid in the 'usertype' table, which is 'admin'
-- 2 = menu id 2, which is 'View Users'

-- 3 = record id
-- 2 = userpagetypeid 2 is usertype...
-- 2 = ...the above line tells us this record belongs to usertypeid in the 'usertype' table, which is 'finance'
-- 1 = menu id 1, which is 'Home'

-- 4 = record id
-- 2 = userpagetypeid 2 is usertype...
-- 2 = ...the above line tells us this record belongs to usertypeid in the 'usertype' table, which is 'finance'
-- 3 = menu id 3, which is 'View Financial Reports'

"Wisdom is knowing what to do next, skill is knowing how to do it, and virtue is doing it. "

"The questions you ask consistently will create either enervation or enjoyment, indignation or inspiration, misery or magic. Ask the questions that will uplift your spirit and push you along the path of human excellence. "

K.I.S.S - Keep it simple stupid

raclede™

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources