September 15, 2005 at 7:31 am
SELECT m.module_id, p.page_name, p.page_url
FROM user_permissions up RIGHT OUTER JOIN
module m on up.module_id = m.module_id INNER JOIN
module_content mc on m.module_id = mc.module_id INNER JOIN
page p on mc.page_id = p.page_id
WHERE up.users_id = @userid
When passed the users ID it returns only the rows they have permission to
What I am hoping I can do (with the help from you all)
Is get one query that will return all the m.module_id's and a way to distinguish which one the user has permissions to
I want to return all modules a populate them into a checkbox type list and have the checkboxes checked but that code is another story.
ddl below
CREATE TABLE [dbo].[module] (
[module_id] [int] IDENTITY (1, 1) NOT NULL ,
[name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[module_content] (
[module_id] [int] NOT NULL ,
[page_id] [bigint] NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[page] (
[page_id] [bigint] IDENTITY (1, 1) NOT NULL ,
[page_name] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[page_url] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[user_permissions] (
[users_id] [bigint] NOT NULL ,
[module_id] [int] NOT NULL
) ON [PRIMARY]
INSERT into module (name)
1,"csrBreak"
2, "disatchBreak"
INSERT into module_content (module_id, page_id)
1, 1
2, 2
INSERT into page (name, url)
1, "csrBreak schedule", "/CSR/BreakTime.aspx"
2, "Dispatch Break Schedule", "/Dispatch/BreakTime.aspx"
INSERT into user_permissions (user_id, module_id)
1, 2
INSERT into users (username, specialpermissions)
1, "hruser", 1
So basically as you can see user 1 had rights to see module 1 but not module 2 well I would like to out put both modules names (module.name) and have some way to know that the user passed via the variable in the tored procedure (@userID) has permissions to module_id 1 but not 2.
Does that help any?
September 15, 2005 at 9:35 am
If you mix Left/Right joins with inner joins (only on referencing tables) it has a nullifying effect.
Not Tested
SELECT m.module_id, p.page_name, p.page_url
FROM user_permissions up
RIGHT OUTER JOIN module m on up.module_id = m.module_id
RIGHT OUTER JOIN module_content mc on m.module_id = mc.module_id
RIGHT OUTER JOIN page p on mc.page_id = p.page_id
WHERE up.users_id = @userid
September 15, 2005 at 9:47 am
still gave me the same result - only gave me the modules that the user had not all of them.
Would this be better done in the code side by comparing the results of 2 seperate queries?
September 15, 2005 at 10:01 am
Rearrange the query a little.
When you are filtering on a record that is in one of the left/right join tables, it also has a null effect on the Outer join so make the where clause part of the join criteria.
-- M.module ID is the module, up.Module_ID if null False, not null then True
SELECT m.module_id, up.Module_ID, p.page_name, p.page_url
FROM module m
left OUTER JOIN user_permissions up on up.module_id = m.module_id and up.users_id = @User_ID
left OUTER JOIN module_content mc on m.module_id = mc.module_id
left OUTER JOIN page p on mc.page_id = p.page_id
September 15, 2005 at 10:09 am
Pefect it works great - thank you very much I never knew you could place criteria in the join commands - thanks again
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply