how can we search record from mlm table structure.

Question

1.00/5 (1 vote)

See more:

I have a table with data.

ID  ParentID    NodeName
1   NULL    Administration
2   NULL    Master Data
3   NULL    Input Forms
4   NULL    User Reports
5   NULL    Other Pages
6   1   Add User
7   2   Product Maintanence
8   2   Product BOM
9   3   Expected Sales
10  3   Product BOM
11  4   Finance
12  4   Manufacturing
13  6   GOGS Report
14  7   Purchase History
15  8   Production Report
16  5   Google
17  5   Company Site

Now I want to write a query which distinguish above query result as per parent-child relationship, as Parent_Original>>Parent1>>Child. If the database child goes upto n level it also result like Parent n> Parent n-1> Parent n-2 > ... > Last Child.

In above table scenario it result like.

Parent              Parent-1               Child

Administration      Add User               GOGS Report
Master Data         Product Maintanence    Purchase History
Master Data         Product BOM            Production Report
........... so on

Can any one suggest me how can we do this. Any suggestion really appreciate.

Posted 10-Jun-13 3:55am

Mas11

Updated 10-Jun-13 3:57am

CHill60

v2

Add a Solution

3 solutions

Solution 1

Makeing difficult query (MLM pyramid)

Posted 10-Jun-13 5:32am

Ahmed Bensaid

Comments

Mas11 11-Jun-13 3:32am

Hi ! Thanks for ur answer.

Solution 2

Try recursive CTE's or form below query dynamically and execute

SQL

select 	
	p2.Nodename,
	p1.Nodename,
	p.Nodename
from [tablem] p (nolock)
left join [tablem] p1 (nolock) on p.parentid=p1.id
left join [tablem] p2 (nolock) on p1.parentid=p2.id
where p2.Nodename is not null
AND p1.Nodename is not null
AND p.Nodename is not null

Posted 10-Jun-13 5:44am

hitesh_tech

Comments

Mas11 11-Jun-13 3:32am

Hi Hitesh ! Thanks for ur answer but it doesn't give me exact solution.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Mas11 · Accepted Answer · 2013-06-11T00:25:00

Hi guys ! I have solved this with the help of one of the brilliant guy. Please see solution below & enjoy with this solution if you'll face such type of task.

SQL

select r.nodename as root
      ,c1.nodename as [child-1]
      ,c2.nodename as [child-2]
      ,c3.nodename as [child-3]
      ,c4.nodename as [child-4]
      ,c5.nodename as [child-5]
from   @tbl r
       left outer join @tbl c1 on r.id = c1.parentid
       left outer join @tbl c2 on c1.id = c2.parentid
       left outer join @tbl c3 on c2.id = c3.parentid
       left outer join @tbl c4 on c3.id = c4.parentid
       left outer join @tbl c5 on c4.id = c5.parentid
where  r.parentid is null
order by r.nodename, c1.nodename, c2.nodename, c3.nodename, c4.nodename, c5.nodename