连接两个表的问题

Question

我目前正在使用C＃编写应用程序，但在连接两个表时遇到了一些困难。为了使事情更清楚，这里是我的表结构

表1（员工名单）

| EmployeeID | EmployeeName |
+------------+--------------+
|     1      | John Smith   |
|     2      | Ian Smosh    |

表2（推荐名单）

| PersonalID | InviterID | InterviewerID | 
+------------+-----------+---------------+
|     1      |   1       |       1       | 
|     2      |   1       |       2       |

Datagridview上的输出应为

| Employee Name | Invites | Interviews | 
+---------------+---------+------------+
| John Smith    | 2       |      1     | 
| Ian Smosh     | 0       |      1     |

我目前可以同时获得邀请，但不能同时获得采访。我只能得到一个。

这就是我得到的

| Employee Name | Invites | 
+---------------+---------+
|  John Smith   |  2      | 
|  Ian Smosh    |  0      |

这是我的代码：

SELECT Table1.RecruiterName AS Name, 
    COUNT(Table2.InviterID) AS Invites, 
    COUNT(Table2.InterviewID) AS Interviews 
FROM Table2 LEFT JOIN Table1 ON Table2.InviterID = Table1.EmployeeID 
    AND Table2.InterviewerID = Table1.InviterID 
GROUP BY EmployeeName

那里的人知道我的代码有什么问题吗？

更新：我设法使它更好一点，但我不断

| Employee Name | Invites | Interviews | 
+---------------+---------+------------+
| John Smith    | 2       |      2     | 
| Ian Smosh     | 0       |      1     |

John Smith的条目只有2个邀请和1个面试。这是我当前的代码

SELECT Recruiters.RecruiterName AS Name, COUNT(Source.SourceID) AS Source, COUNT(Interview.InterviewID) AS Interview 
FROM Recruiters 
LEFT JOIN Hires Source ON Source.SourceID=Recruiters.RecruiterID 
LEFT JOIN Hires Interview ON Interview.InterviewID=Recruiters.RecruiterID
GROUP BY RecruiterName

为什么约翰·史密斯在面试中得到的金额不正确，但伊恩·斯莫什（Ian Smosh）是正确的。

Answer 1

双连接是双浸
这应该工作

select employee.EmployeeName, inv.count, int.count 
  from employee 
  join ( select InviterID, 
                count(*) as count 
           from referral 
          group by InviterID     ) as inv 
    on employee.employeeID = inv.InviterID 
  join ( select InterviewerID, 
                count(*) as count 
           from referral 
          group by InterviewerID ) as int 
    on employee.employeeID = int.InterviewerID