0

I got two tables called: EmployeeTable & TaskAssignmentTable. They look like this : tables formation
TaskAssignmentTable shows tasks assigned to employees. In order to assign new tasks to employees i want to have count of tasks assigned to different people and then assign task to people who have least tasks assigned.
Problem: using normal count() on TaskAssignmentTable results in this table: enter image description here
But what i want is some sort of join between tables which shows count of rows which are present in first table and absent in 2nd table with count equal to 0 like this one:

enter image description here

So what would be the SQL query to join tables and do such thing? (Optional: Since I'm using C# Linq-2-SQL i would be grateful if someone can write LINQ syntax for this).

VSB
  • 9,825
  • 16
  • 72
  • 145
  • Show us something that you have tried? – Sadique Mar 13 '14 at 10:12
  • i don't know how to use join of tables and count on 2nd table together – VSB Mar 13 '14 at 10:17
  • FKemployeeid is an int or a string?? could you provide a sqlfiddle – giammin Mar 13 '14 at 10:26
  • What you need is an outer join: read my answer: https://stackoverflow.com/questions/22375246/sql-count-rows-which-are-not-present-in-table#answer-22375774 – giammin Mar 13 '14 at 10:35
  • Do you want an SQL answer or a Linq2Sql answer? The translation can be surprisingly "non idiomatic" in Linq2Sql. If a pure SQL answer is sufficient/desired, remove the C#/linq-to-sql tags. – user2864740 Mar 13 '14 at 10:40
  • linq answer would be preferred but not mandotary. – VSB Mar 13 '14 at 10:51

4 Answers4

2

You need a LEFT OUTER JOIN based upon your statement that you want rows that are present in the first table but not the second:

SELECT EmployeeID, Name, Count(TaskID) as CNT
FROM EmployeeTable e
LEFT JOIN TaskAssignmentTable t 
    ON e.employeeID = t.FKEmployeeID
GROUP BY EmployeeID, Name
Steve Ford
  • 7,433
  • 19
  • 40
1

Try

SELECT EmployeeID, Name, Count(TaskID) as CNT
FROM EmployeeTable emp
LEFT JOIN TaskAssignmentTable task on emp.employeeID = task.FKEmployeeID
GROUP BY EmployeeID, Name
DNac
  • 2,663
  • 8
  • 31
  • 54
  • i tested your statement but it does not return any rows with count =0. I Mean it still does not present rows absent in 2nd table. – VSB Mar 13 '14 at 10:30
  • my snippet was: `SELECT emp.id, Name, Count(taskAssignment.taskID) as CNT FROM employee emp JOIN taskAssignment on emp.id = taskAssignment.FKEmployeeID GROUP BY emp.id, Name` – VSB Mar 13 '14 at 10:31
  • Try it with LEFT JOIN. Updated my answer. – DNac Mar 13 '14 at 10:33
1

For that you have to use Left Outer Join.

SELECT EmployeeID, Name, Count(TaskID) as CNT
FROM EmployeeTable emp
LEFT OUTER JOIN TaskAssignmentTable task on emp.employeeID = task.FKEmployeeID
GROUP BY EmployeeID, Name

And LINQ Version of this query look like this

var employees = from emp in dbContext.Employees
                join task in dbContext.TaskAssignmentTable 
                on emp.employeeID equals task.FKEmployeeID
                into tEmpWithTask
                from tEmp in tEmpWithTask.DefaultIfEmpty()
                group tEmp by new { emp.EmployeeID, emp.Name } into grp
                select new {
                  grp.Key.EmployeeID,
                  grp.Key.Name,
                  grp.Count(t=>t.TaskID != null)
                };
Krishnraj Rana
  • 6,516
  • 2
  • 29
  • 36
0

You need to OUTER JOIN the two table (in your case a LEFT JOIN):

SELECT EmployeeID, Name, Count(TaskID) as CNT
FROM EmployeeTable emp
LEFT JOIN TaskAssignmentTable task on emp.employeeID = task.FKEmployeeID
GROUP BY EmployeeID, Name
Community
  • 1
  • 1
giammin
  • 18,620
  • 8
  • 71
  • 89