July 4, 2012 at 3:07 am
Hi,
I have a Menu Table call as Modules with the columns as mention below
CREATE TABLE #Modules
(
ModuleIdint,
Captionvarchar(20),
SourceNamevarchar(20),
MenuLevelint,
ParentIdint
)
insert into #Modules
Select 1, 'Test 1', null, 1, 0
UNION
Select 2, 'Test 2', 'Form2', 2, 1
UNION
Select 3, 'Test 3', 'Form3', 2, 1
UNION
Select 4, 'Test 4', 'Form4', 2, 1
UNION
Select 5, 'Test 5', null, 1, 0
UNION
Select 6, 'Test 6', null, 2, 5
UNION
Select 7, 'Test 7', 'Form7', 3, 6
UNION
Select 8, 'Test 7', 'Form7', 3, 6
And I have a Right table named as ModuleRight with the column mention as
CREATE TABLE #ModuleRight
(
ModuleIdint
Roleid int
)
Insert into #ModuleRight
Select 2, 1
UNION
Select 7, 1
As you can see that there is only two row exist is Right Table which have access to a particular module. No parent row is exist in that table.
Now I want record with below mention structure.
ModuleId , Caption, SourceName, ParentId
1Test 1null0
2Test 2Form21
5Test 5null0
6Test 6null 5
7Test 7Form76
I can't work out how to do it.
Any help appreciated and any pointers to good TSQL tutorials and books also appreciated as I need to do some (a lot) of learning. Can Some one tell me how to achieve this with CTE or any other method.
July 4, 2012 at 5:03 am
I believe, I am just too close for that solution...
With Hierarchy (ParentID, AMIID, MenuText, level)
As
(
Select ParentID, m.ModuleId, Caption, 0
from #Modules m
INNER JOIN #ModuleRight p
on m.ModuleId = p.ModuleId
WHERE p.Roleid = 1
Union All
Select e.ParentID, e.ModuleId, e.Caption, level + 1
From #Modules As e
Inner Join Hierarchy As d
On e.ModuleId = d.ParentID
)
Select ParentID, AMIID, MenuText As MenuText, level
From Hierarchy
Order By AMIID
July 4, 2012 at 6:10 am
That Script Provide multiple row with the same data
July 4, 2012 at 11:42 am
I had done this as a Solution
------------------------------
CREATE TABLE #ModuleRight
(
ModuleIdint,
Roleid int
)
Insert into #ModuleRight
Select 2, 1
UNION
Select 7, 1
Create Table #tmp_modules
(
[Access][bit] NOT NULL ,
[module_id][int] NOT NULL ,
[Caption]varchar(50) null,
[source_name][varchar] (100) NULL ,
[module_menu_level] [int] NULL ,
[parent_module_id][int] NULL ,
)
INSERT into #tmp_modules
select case when p.ModuleId is null then 0 else 1 end as Access, m.ModuleId, m.Caption, m.sourcename, m.MenuLevel, m.ParentId
from #Modules m
LEFT OUTER JOIN #ModuleRight p
on m.ModuleId = p.ModuleId
AND p.Roleid = 1
declare @LevelCount as smallint
Select @LevelCount = max(module_menu_level)
from #tmp_modules
while @LevelCount > 0
Begin
UPDATE #tmp_modules set Access = 1
from (
select distinct parent_module_id module_id
from #tmp_modules
where Access = 1
and module_menu_level = @LevelCount
) A
where #tmp_modules.module_id = A.module_id
set @levelCount = @levelCount -1
End
select Module_Id , Caption, isnull(source_name,'') Source_Name, Module_Menu_Level, isnull(Parent_Module_Id, 0) Parent_Module_Id
from #tmp_modules A
where Access =1
order by parent_module_id asc
DROP TABLE #tmp_modules
May be someone can provide batter solution for that.
July 4, 2012 at 9:49 pm
No replay from anyone...
July 4, 2012 at 11:19 pm
Vijay - I think we can be a little more succinct and avoid any unnecessary temporary tables like this:
;WITH MyModules AS (
SELECT m.ModuleID, Caption, SourceName, MenuLevel, ParentID
FROM #Modules m
INNER JOIN #ModuleRight mr ON m.ModuleID = mr.ModuleId
UNION ALL
SELECT m.ModuleID, m.Caption, m.SourceName, m.MenuLevel, m.ParentID
FROM #Modules m
INNER JOIN MyModules mm ON mm.ParentID = m.ModuleID
WHERE mm.ParentID <> 0
)
SELECT ModuleID, Caption, SourceName, ParentID
FROM MyModules
ORDER BY ModuleID
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
July 4, 2012 at 11:38 pm
Dwain,
Your solution is working fine with sample data, but when I applied it to large data giving wrong result.
Means provide the multiple parent row for the given child row.
Vijay
July 4, 2012 at 11:49 pm
vijay.s (7/4/2012)
Dwain,Your solution is working fine with sample data, but when I applied it to large data giving wrong result.
Means provide the multiple parent row for the given child row.
Vijay
Can you post more sample data then with some failures?
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
July 4, 2012 at 11:51 pm
Just insert below mention data and see the expected result
insert into #Modules
Select 21, 'Test 2', 'Form2', 2, 1
Insert into #ModuleRight
Select 21, 1
It will give multiple parent row.
ModuleID,Caption,SourceName,ParentID
1,Test 1,NULL,0
1,Test 1,NULL,0
2,Test 2,Form2,1
5,Test 5,NULL,0
6,Test 6,NULL,5
7,Test 7,Form7,6
21,Test 2,Form2,1
insted of
ModuleID,Caption,SourceName,ParentID
1,Test 1,NULL,0
2,Test 2,Form2,1
5,Test 5,NULL,0
6,Test 6,NULL,5
7,Test 7,Form7,6
21,Test 2,Form2,1
It is taking same parent row for each child row, so thats make multiple parent row
July 5, 2012 at 12:12 am
I'm not seeing the same results as you. Try this:
CREATE TABLE #Modules
(
ModuleIdint,
Captionvarchar(20),
SourceNamevarchar(20),
MenuLevelint,
ParentIdint
)
insert into #Modules
Select 1, 'Test 1', null, 1, 0
UNION Select 2, 'Test 2', 'Form2', 2, 1
UNION Select 3, 'Test 3', 'Form3', 2, 1
UNION Select 4, 'Test 4', 'Form4', 2, 1
UNION Select 5, 'Test 5', null, 1, 0
UNION Select 6, 'Test 6', null, 2, 5
UNION Select 7, 'Test 7', 'Form7', 3, 6
UNION Select 8, 'Test 7', 'Form7', 3, 6
UNION Select 21, 'Test 2', 'Form2', 2, 1
CREATE TABLE #ModuleRight
(
ModuleIdint,
Roleid int
)
Insert into #ModuleRight
Select 2, 1 UNION ALL Select 7, 1
SELECT * FROM #Modules
--SELECT * FROM #ModuleRight
--Desired result:
--ModuleId , Caption, SourceName, ParentId
--1 Test 1 null 0
--2 Test 2 Form2 1
--5 Test 5 null 0
--6 Test 6 null 5
--7 Test 7 Form7 6
;WITH MyModules AS (
SELECT m.ModuleID, Caption, SourceName, MenuLevel, ParentID
FROM #Modules m
INNER JOIN #ModuleRight mr ON m.ModuleID = mr.ModuleId
UNION ALL
SELECT m.ModuleID, m.Caption, m.SourceName, m.MenuLevel, m.ParentID
FROM #Modules m
INNER JOIN MyModules mm ON mm.ParentID = m.ModuleID
WHERE mm.ParentID <> 0
)
SELECT ModuleID, Caption, SourceName, ParentID
FROM MyModules
ORDER BY ModuleID
DROP TABLE #Modules, #ModuleRight
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
July 5, 2012 at 12:18 am
I think u missed below mention step.
Just assign one module right more for the same parent id
Insert into #ModuleRight
Select 21, 1
and Check the result. :-). Giving multiple row.
July 5, 2012 at 12:22 am
Must've indeed. Try this:
;WITH MyModules AS (
SELECT m.ModuleID, Caption, SourceName, MenuLevel, ParentID
FROM #Modules m
INNER JOIN #ModuleRight mr ON m.ModuleID = mr.ModuleId
UNION ALL
SELECT m.ModuleID, m.Caption, m.SourceName, m.MenuLevel, m.ParentID
FROM #Modules m
INNER JOIN MyModules mm ON mm.ParentID = m.ModuleID
WHERE mm.ParentID <> 0
)
SELECT DISTINCT ModuleID, Caption, SourceName, ParentID
FROM MyModules
ORDER BY ModuleID
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
July 5, 2012 at 12:29 am
Yes Distinct Can Work but If we modify our query for finding dynamic level then same will fail.
;WITH MyModules AS (
SELECT m.ModuleID, Caption, SourceName, 0 MenuLevel, ParentID
FROM #Modules m
INNER JOIN #ModuleRight mr ON m.ModuleID = mr.ModuleId
UNION ALL
SELECT m.ModuleID, m.Caption, m.SourceName, mn.MenuLevel + 1 MenuLevel, m.ParentID
FROM #Modules m
INNER JOIN MyModules mm ON mm.ParentID = m.ModuleID
WHERE mm.ParentID <> 0
)
SELECT DISTINCT ModuleID, Caption, SourceName, ParentID
FROM MyModules
ORDER BY ModuleID
July 5, 2012 at 12:43 am
vijay.s (7/5/2012)
Yes Distinct Can Work but If we modify our query for finding dynamic level then same will fail.
;WITH MyModules AS (
SELECT m.ModuleID, Caption, SourceName, 0 MenuLevel, ParentID
FROM #Modules m
INNER JOIN #ModuleRight mr ON m.ModuleID = mr.ModuleId
UNION ALL
SELECT m.ModuleID, m.Caption, m.SourceName, mn.MenuLevel + 1 MenuLevel, m.ParentID
FROM #Modules m
INNER JOIN MyModules mm ON mm.ParentID = m.ModuleID
WHERE mm.ParentID <> 0
)
SELECT DISTINCT ModuleID, Caption, SourceName, ParentID
FROM MyModules
ORDER BY ModuleID
You'll need to elaborate further and probably provide additional examples if you expect me to understand that.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
July 5, 2012 at 1:39 am
I had done something different with 100% assurance for output. May be same is lengthy process but not take much time.
Create Table #tmp_modules
(
[Access][bit] NOT NULL ,
[module_id][int] NOT NULL ,
[Caption]varchar(50) null,
[source_name][varchar] (100) NULL ,
[module_menu_level] [int] NULL ,
[parent_module_id][int] NULL ,
)
INSERT into #tmp_modules
select case when p.ModuleId is null then 0 else 1 end as Access, m.ModuleId, m.Caption, m.sourcename, m.MenuLevel, m.ParentId
from #Modules m
INNER JOIN #ModuleRight p
on m.ModuleId = p.ModuleId
WHERE AND p.Roleid = 1
if object_id('tempdb..#moduleid') IS NOT NULL drop table #moduleid;
Select MAX(module_id) moduleid
into #moduleid
from #tmp_modules
Group By parent_module_id
if object_id('tempdb..#access') IS NOT NULL drop table #access;
create table #access ( AccessModule int)
;WITH AllModules AS --setting up the CTE
(
Select Parent_Module_Id, Module_Id, Caption Collate SQL_Latin1_General_CP1_CI_AS Caption, Source_Name Collate SQL_Latin1_General_CP1_CI_AS Source_Name, module_menu_level
from #tmp_modules m
inner JOIN #moduleid p
on m.Module_Id = p.ModuleId
UNION ALL
Select c.ParentId, c.ModuleId, c.Caption Collate SQL_Latin1_General_CP1_CI_AS Caption,
c.SourceName Collate SQL_Latin1_General_CP1_CI_AS SourceName, c.module_menu_level
from AllModules p
inner join #Modules c
on c.ModuleId = p.Parent_Module_Id
)
INSERT INTO #access
SELECT DISTINCT module_id
FROM AllModules
INSERT INTO #access
SELECT DISTINCT module_id from #tmp_modules
select *
from #Modules A where ModuleId in (select Distinct AccessModule from #access)
order by parentid asc
I Know that is some lengthy process, but remove some futuristic error.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply