Problems with joining two tables

Question

I am currently making an application using C # and I am having difficulty joining two tables. To make things clearer, table structures are presented here.

Table 1 (list of employees)

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

Table 2 (List of referrals)

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

The output on the datagridview should be

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

I can currently receive invitations, but not interviews. I can only get it.

This is what i get

 | 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 

Does anyone know what happened to my code?

UPDATE: I managed to improve a bit, but I keep getting

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

The record for John Smith has only 2 invitations 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 

Why John Smith is mistaken in an interview, but Ian Smosh is right.

Answer 1

double connection - double immersion 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 

Answer 2

 SELECT Recruiters.RecruiterName AS Name, (select COUNT(*) from Hires where SourceID = Recruiters.RecruiterID) AS Source, (select COUNT(*) from Hires where InterviewID = Recruiters.RecruiterID) AS Interview FROM Recruiters