Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C#4.0
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, RightBlush | :O
 
if 1003 is input
The output should be : Left:3, RightBlush | :O
 
if 1004 is input
The output should be : LeftBlush | :O , RightBlush | :O
 
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 3:45am

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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..
 
<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
  Permalink  
Comments
sentimental37 at 14-Nov-13 23:53pm
   
Sorry.But this code is not giving correct output.
 
the code used by me is
 
<pre lang="SQL">
DECLARE @EmpId INT;
SELECT @EmpId = 1001;

WITH Parent AS
(
SELECT HE.Id, HE.Name, HE.Gender, HE.SponsorID, HE.PairSide
FROM ds_users HE
WHERE HE.Id = @EmpId
UNION ALL
SELECT HE.Id, HE.Name, HE.Gender, HE.SponsorID , HE.PairSide
FROM ds_users HE INNER JOIN Parent
On HE.Id = Parent.SponsorID
WHERE
HE.Id != Parent.Id
),
Children
AS
(
SELECT * FROM Parent
UNION ALL
SELECT HE.Id, HE.Name, HE.Gender, HE.SponsorId , HE.PairSide
FROM ds_users HE
INNER JOIN Children ON HE.SponsorID = Children.Id
WHERE
HE.Id != Children.Id
)
SELECT PairSide , COUNT(MLM.PairSide) FROM
(
SELECT * FROM Parent --GROUP BY Side
UNION
SELECT * FROM Children --GROUP BY Side
) MLM
GROUP BY PairSide
</pre>
 

In each case it is giving 6 in left and 3 in right.
 
My database content is like this
 
Id Name Gender SponsorID PairSide
1000 S&Y Solutions M 0 NULL
1001 Vishal Singh M 1000 Left
2011 Vishal M 1001 Left
2012 Vishal M 1001 Left
2013 Vivek M 1001 Right
2014 Rana M 1001 Left
2015 Vishal Singh M 1001 Left
2016 Rana M 1001 Right
2017 Vivek M 1001 Right
2018 Vishal M 2017 Left
Jobless Creature at 16-Nov-13 6:15am
   
What is the expected output? As I mentioned, in the initial question, the expected output is not printed properly for all cases. Can you add some more records and share the expected output as well?
Volynsky Alex at 17-Apr-14 7:34am
   
Nice

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



Advertise | Privacy | Mobile
Web01 | 2.8.1411022.1 | Last Updated 16 Apr 2014
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100