How to retrive all the child elements from a root element.

Question

0.00/5 (No votes)

See more:

suppose i have added one customer details in a table.

like

AgentCode AgentName Contact	City	IntroCode Introname
A0001	       Mahesh 	8130605243	Delhi		
A0002	       suresh	9077878787	sonpur	A0001	Mahesh kumar pattnayak
A0003	       rajeev	4353453453	delhi	A0001	Mahesh kumar pattnayak
A0004	       pradeep	992932939	KANPUR	A0003	rajeev
A0005	       VISHAL	92394929394	LUCKNOW	A0002	suresh
A0006	       KARTHIK	92394929394	HYD	A0002	suresh
A0007	       Abhinav	929929394	assam	A0001	Mahesh kumar pattnayak
A0008	       rajesh	3453453453	rajnag  A0003	rajeev
A0009	       Sanjit	923499234	Delhi	A0001	Mahesh kumar pattnayak
A0010	       Dinesh	99399293	delhi	A0004	pradeep

now here we have agent code, name, contact, city, intro. code and intro. name.
intro. code is the code of the agent who have added the agent ...

now in c#. i want to acess the child elements from the table.
for ex. if i enter A0002 - then it show all the elements under the Agent Code A0002. It's like a tree structure. but how to acess all the child elements. i don't know what to do ?

Posted 17-Apr-15 0:44am

Mahesh Pattnayak

Add a Solution

Comments

Andy Lanng 17-Apr-15 6:59am

Interesting question, I think we can help.
before we can, please clarify a couple of points and include the answers in your question by clicking 'Improve question' just above this comment.

1: Please post your code. Table and child elements could mean many things so show us the context of the terms your using.
2: For extra points, show us an example of what you have and an example of what you need. Any representation will do.
Thanks ^_^

Mahesh Pattnayak 19-Apr-15 13:01pm

AgentCode AgentName Contact City IntroCode Introname
A0001 Mahesh 8130605243 Delhi
A0002 suresh 9077878787 sonpur A0001 Mahesh kumar pattnayak
A0003 rajeev 4353453453 delhi A0001 Mahesh kumar pattnayak
A0004 pradeep 992932939 KANPUR A0003 rajeev
A0005 VISHAL 92394929394 LUCKNOW A0002 suresh
A0006 KARTHIK 92394929394 HYD A0002 suresh
A0007 Abhinav 929929394 assam A0001 Mahesh kumar pattnayak
A0008 rajesh 3453453453 rajnag A0003 rajeev
A0009 Sanjit 923499234 Delhi A0001 Mahesh kumar pattnayak
A0010 Dinesh 99399293 delhi A0004 pradeep

This is the table.

if i enter the agent code A0002, then it show the the results as below.
it will show the details of agents who have added under A0002.

A0005 // ADDED UNDER A0002
A0006 // ADDED UNDER A0002
NOW If any agent added under A0005, or A0006, THEN that agent also be shown.

Mahesh Pattnayak 19-Apr-15 13:02pm

please help sir ... i need it in my project.

3 solutions

Solution 3

Another option, if you're using Microsoft SQL Server 2008 or higher, would be the hierarchyid type[^]:
Hierarchical Data (SQL Server)[^]
Tutorial: Using the hierarchyid Data Type[^]
Hierarchy ID in SQL Server[^]

Posted 17-Apr-15 3:47am

Richard Deeming

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)

Arkadeep De · Accepted Answer · 2015-04-17T02:51:00

Solution 1

Its like the MLM(Multi Level Marketing)...Here is a solution for your query.
Recursive Queries in Microsoft SQL Server 2008[^]

Posted 17-Apr-15 2:51am

Arkadeep De

Marcin Kozub · Accepted Answer · 2015-04-17T03:36:00

Hi,

In project i'm currently developing, i'm using additional columns in database tables which has tree structure. Columns are named: TreePath and TreeLeveel. I'm using SQL Server and both columns are automatically filled using database triggers on insert or update.

TreePath column is used to store path to elements and TreeLevel is used to store depth of element in tree which in your case could look like this:

AgentCode	IntroCode	TreeLevel	TreePath
A0001				    0		.A0001.
A0002		A0001		1		.A0001.A0002.	
A0003		A0001		1		.A0001.A0003.
A0004		A0003		2		.A0001.A0003.A0004
and so on...

In this case you don't have to use recursion and complicated queries to retrieve rercord with childs. So, if you want to get records for A0002 and all childs you can use simple select:

SQL

SELECT * FROM YOUR_TABLE_NAME WHERE TreePath LIKE '.A0002%'

I hope it help you :)