GET Values from table in a recursive manner for MLM based project

Question

1.00/5 (1 vote)

See more:

I am trying to work with C# and i am stuck here. My problem is i want to retrieve the count of all the users in a specific side. Let me Explain. Here is my table snapshot with sample data.

[URL=http://imageshack.us/photo/my-images/809/zdta.png/][IMG]http://imageshack.us/scaled/thumb/809/zdta.png[/IMG][/URL]

Now what i want exactly is that i get the count of users in a specific side as i give a user id as a parameter.The data is stored in MS Sql Database and i have to implement this on a website so efficiency of code is also a issue.

Suppose i give 1001 as input
The output should be : Left:1 , Right: 8

if 1002 is input
The output should be : Left:5, Right:0

if 1003 is input
The output should be : Left:3, Right:0

if 1004 is input
The output should be : Left:0, Right:0

if 1005 is input
The output should be : Left:1, Right:1

and so on. So please provide me some logic on how to implement this thing.
I also want to get a code which would return an array of all child users of a particular ID in a hierarchical manner so that i could process that data with RadOrgChart Control.
Please help me.its very critical

Posted 14-Nov-13 2:45am

Vishal.KumarSingh

Add a Solution

1 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)

Jobless Creature · Answer 1 · 2013-11-14T03:51:00

This I think can be solved by using a Common Table Expression.

Check this TIP / TRICK - Common Table Expression to find all related nodes[^]

I modified the script attached with the TIP and added the 'Side' column and modified the CTE query and I think it works.. But am not sure since your example inputs are shown as a smiley(!) in the question.. Given below is the CTE I wrote..

Let me know if this works..

SQL

<pre>
--CTE Query to get the hierarchy
DECLARE @EmpId INT;
SELECT @EmpId = 5;

WITH Parent AS 
(
	SELECT HE.EmpId, HE.EmpName, HE.EmpAge, HE.ManagerID, HE.Side 
	FROM EmpMLM HE
	WHERE HE.EmpId = @EmpId
	UNION ALL
	SELECT HE.EmpId, HE.EmpName, HE.EmpAge, HE.ManagerID , HE.Side
	FROM EmpMLM HE INNER JOIN Parent 
	On HE.EmpId = Parent.ManagerID
	WHERE 
	HE.EmpId != Parent.EmpId
), 
Children 
AS
(
	SELECT * FROM Parent 
	UNION ALL
	SELECT HE.EmpId, HE.EmpName, HE.EmpAge, HE.ManagerID , HE.Side  
	FROM EmpMLM HE
	INNER JOIN Children ON HE.ManagerID = Children.EmpId
	WHERE 
	HE.EmpId != Children.EmpId
)
SELECT SIDE , COUNT(MLM.Side) FROM 
(
SELECT * FROM Parent --GROUP BY Side
UNION 
SELECT * FROM Children --GROUP BY Side
) MLM
GROUP BY Side