Click here to Skip to main content
14,486,704 members
Rate this:
Please Sign up or sign in to vote.
See more:
profile
TableAId profile  prof_type  groupId   division     Department     unitBranch
 1        prof1     1           1          1            1              1
 2        prof2     2           1          1            1              1
 3        prof3     1           2          1            3              1
 4        prof4     1           1          1            3              1


staff
TableBId   staff          groupId   division     Department     unitBranch
  1         ali            1          1            1              1
  2         Mohammad       2          1            1              1              
  3         ahmad          1          1            3              1
  4         omar           1          1            3              1
  5         othman         1          1            1              1
  6         basam          1          1            3              1
  7         saaed          3          1            3              1


I am trying to create list like
groupId   division     Department     unitBranch     COUNT
     1          1            1              1          2
     2          1            3              1          1
     1          1            3              1          3

This is the query which I am trying to create but I am getting wrong results.

var tes = (from a in db.Profiles
                       join b in db.Staffs on
                       new { a.GroupID, a.DivisionID, a.DepartmentID, a.UnitBranchID }
                       equals
                       new { GroupID = b.level1, DivisionID = b.level2, DepartmentID = b.level3, UnitBranchID = b.level4 } into joinedValues
                       group joinedValues by a into g
                       select new
                       {
                           Profile = g.Key.Profile,
                           count = g.Count()
                       }).ToList();


Please Help to write query return count of staff where (groupID ,division ,department , unitbranch) in tableA = Table B .

What I have tried:

I am tired to write query return count of staff
Posted
Updated 22-Mar-20 6:56am
Rate this:
Please Sign up or sign in to vote.

Solution 2

Seems, you have to group by two fields: groupId and Department.

DataTable dt = new DataTable();
dt.Columns.AddRange(new DataColumn[]{
	new DataColumn("TableBId", typeof(int)),
	new DataColumn("staff", typeof(string)),
	new DataColumn("groupId", typeof(int)),
	new DataColumn("division", typeof(int)),
	new DataColumn("Department", typeof(int)),
	new DataColumn("unitBranch", typeof(int))
});
dt.Rows.Add(new object[]{1, "ali", 1, 1, 1, 1});
dt.Rows.Add(new object[]{2, "Mohammad", 2, 1, 1, 1});
dt.Rows.Add(new object[]{3, "ahmad", 1, 1, 3, 1});
dt.Rows.Add(new object[]{4, "omar", 1, 1, 3, 1});
dt.Rows.Add(new object[]{5, "othman", 1, 1, 1, 1});
dt.Rows.Add(new object[]{6, "basam", 1, 1, 3, 1});
dt.Rows.Add(new object[]{7, "saaed", 3, 1, 3, 1});

var data = dt.AsEnumerable()
	.GroupBy(x=>new {groupId = x.Field<int>("groupId"), Department = x.Field<int>("Department")})
	.Select(grp=> new 
	{
		groupId = grp.Key.groupId,
		division = grp.Select(y=>y.Field<int>("division")).First(),
		Department = grp.Key.Department,
		unitBranch = grp.Select(y=>y.Field<int>("unitBranch")).First(),
		Cnt = grp.Count()
	});


Result:
groupId division Dep... unitBranch Cnt
1		1		1		1			2 
2		1		1		2			1 
1		1		3		1			3 
3		1		3		3			1 
   
v2
Rate this:
Please Sign up or sign in to vote.

Solution 1

Question: Why are you using the profiles table ?
All the fields you need are in the staff table.

Have a look here: SQL GROUP BY Statement[^]
   
Comments
Waleed Abukatab 22-Mar-20 12:19pm
   
Thanks for helping me, now i have this fields (group, section, unit, section) From these fields I want to find the number of staff in the table staff ,Knowing that there are these fields in (group, section, unit, section) ..?

How can I find a solution to this issue in Linq .
Patrice T 22-Mar-20 12:33pm
   
Sorry, I don't Linq.

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




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