Dear Dwain,

I had apply distinct clause previously but same is giving multiple row as mention earlier.

Actually I have large data (in thousands) so I can't analyze all data one by...

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]...

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,...

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.

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...

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

No replay from anyone...

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 ,...

That Script Provide multiple row with the same data

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...

Yeh..

You are right other wise where ParentId is null condition is enough for me as i have logic in my front end that call only child node. I need...

@cadavre

Thankx, I believe that will solve my problem.

@cadavre

Your query is also returning the same result as mentioned above.

I just want to lowest parent (nth parent) and highest child.

I tried from my side but it will give me following output

WITH

cteMenu AS

(

SELECT ModuleId, ParentId, Caption, Level = 1, HierarchicalPath = CAST(CAST(Caption AS VARCHAR(50)) AS VARCHAR(4000))

...

of course we have sysobjects but firstly understand my question, I have Sql Statement not Sql Object.

Statement like "Select * from TableName"

Vijay