SQL Server 2005 - Nested recursive query :(

Question

I have a query that I need to execute that I do not know how to structure.

I have a table called Employees. I have another table called Company. There is a third table called Files. As you can imagine, a Company has Employees, and Employees have Files.

I need to list out all of the Employees in my database. The challenge is, I need to list the total number of Files in the same company as the Employee. I have tried variations on the following without any luck:

SELECT
  e.FirstName,
  e.LastName,
  e.Company,
  (SELECT COUNT(*) FROM Files f WHERE f.EmployeeID IN (SELECT [ID] FROM Employees e2 WHERE e2.CompanyID=e.CompanyID)) as 'FileCount'
FROM
  Employees e

What am I doing wrong? Thank you!

Answer 1

Try this:

SELECT
  e.FirstName,
  e.LastName,
  e.Company,
  (
    SELECT COUNT(*)
    FROM Files f
    JOIN Employees e2 ON f.EmployeeID = e2.id
    WHERE e2.CompanyID = e.CompanyID
  ) as 'FileCount'
FROM
  Employees e