两个表的连接问题

Question

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

表一（员工名单）

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

Here is my code:

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

为什么约翰史密斯在采访中得到了错误的数量，但伊恩斯莫什是正确的。

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