Query problem - 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 2005 Forums

Transact-SQL (2005)

Query problem

Author

Topic

myrbs
Starting Member

10 Posts

Posted - 2009-03-16 : 22:23:10

I have two different set of select statement one would output this, this are the access rights for my selected group
CHPWD 3 Change Password 1 1 ~/ChangePass.aspx 3 CHPWD
SYMOD 6 System Module 1 1 ~/SystemModule.aspx 5 SYMOD
TEST 4 Test Node 2 2 TEST 2 TEST
LOG 2 Logout 1 1 ~/Logout.aspx 2 LOG
GACL 5 Group ACL 1 1 ~/Group ACL.aspx 4 GACL

and the other one would have a result of this, this one includes all the modules in my system

CHPWD 3 Change Password 1 1 ~/ChangePass.aspx 3 CHPWD
GACL 5 Group ACL 1 1 ~/Group ACL.aspx 4 GACL
LOG 2 Logout 1 1 ~/Logout.aspx 2 LOG
SYMOD 6 System Module 1 1 ~/SystemModule.aspx 5 SYMOD
TEST 4 Test Node 2 2 TEST 2 TEST
TEST2 7 Test Node 2 4 3 TEST 2 TEST2
TEST3 8 testing 1 1 test 6 TEST3
TEST4 9 testing 1 1 test 7 TEST4
TEST7 12 testing 1 1 test 10 TEST7

now the problem is I should join this two so I could be able to know wether my selected group does have access on that module, any recommendations?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2009-03-17 : 03:30:26

Please provide the table structures for the underlying tables in addition to query results.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-17 : 11:30:12

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

myrbs
Starting Member

10 Posts

Posted - 2009-03-18 : 02:24:31

CREATE TABLE [dbo].[tbl_group](
[code] [varchar](5) NOT NULL,
[description] [varchar](60) NOT NULL,
[active_flag] [bit] NOT NULL CONSTRAINT [DF_tbl_group_active_flag] DEFAULT ((1)),
[action_code] [varchar](1) NOT NULL,
[date_maint] [datetime] NOT NULL,
[user_maint] [varchar](5) NOT NULL,
CONSTRAINT [PK_tbl_group] PRIMARY KEY CLUSTERED
(
[code] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[tbl_group_acl](
[group_code] [varchar](5) NOT NULL,
[sys_modules_code] [varchar](8) NOT NULL,
[active_flag] [bit] NOT NULL CONSTRAINT [DF_tbl_group_acl_active_flag] DEFAULT ((1)),
[action_code] [varchar](1) NOT NULL,
[date_maint] [datetime] NOT NULL,
[user_maint] [varchar](5) NOT NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[tbl_group_acl] WITH CHECK ADD CONSTRAINT [FK_tbl_group_acl_tbl_group] FOREIGN KEY([group_code])
REFERENCES [dbo].[tbl_group] ([code])
GO
ALTER TABLE [dbo].[tbl_group_acl] CHECK CONSTRAINT [FK_tbl_group_acl_tbl_group]

CREATE TABLE [dbo].[tbl_sys_modules](
[code] [varchar](8) NOT NULL,
[description] [varchar](60) NOT NULL,
[ctr] [int] NOT NULL,
[tree_level] [int] NULL,
[tree_order] [int] NULL,
[mother_node] [int] NULL,
[mapping] [varchar](60) NULL,
[bread_crumbs] [varchar](100) NULL,
[category] [varchar](50) NULL,
[type] [varchar](1) NOT NULL,
[active_flag] [bit] NOT NULL CONSTRAINT [DF_tbl_sys_modules_active_flag] DEFAULT ((1)),
[action_code] [varchar](1) NOT NULL,
[date_maint] [datetime] NOT NULL,
[user_maint] [varchar](5) NOT NULL,
CONSTRAINT [PK_tbl_sys_modules] PRIMARY KEY CLUSTERED
(
[code] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources