连接两个表的问题 [英] Problems with joining two tables

问题描述

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

I'm currently making an application using C# and I am having some difficulty with joining two tables. To make things more clear here are my table structures

表1(员工名单)

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

表2(引荐列表)

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

Datagridview的输出应为

The output on Datagridview should be

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

我目前能够获得邀请，但不能同时获得采访.我只能拿一个.

I am currently able to get the invites but not the interviews at the same time. I am only able to get one.

这就是我得到的

| 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

那里的任何人都知道我的代码有什么问题吗?

Anyone there knows what's wrong with my code?

更新:我设法使其变得更好一点，但我一直在进步

UPDATE: I managed to make it a little better but I keep getting

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

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

The entry for John Smith only has 2 Invites and 1 Interview. This is my current code

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)是正确的.

Why is it that John Smith gets a wrong amount in interviews but Ian Smosh is correct.

推荐答案

双重连接是双重浸入式
这应该可以工作

the double join is double dipping
this should work

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 

这篇关于连接两个表的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！