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

How about:

SELECT
   e.FirstName,
   e.LastName,
   e.Company,
   select count(*) from Files f, Employees e where f.EmployeeID=e.EmployeeID and e.CompanyID=emp.CompanyID
FROM
  Employees emp

Answer 2

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

Answer 3

A solution with no correlation in SELECT clause. Probably quicker...

SELECT
    e.FirstName,
    e.LastName,
    e.Company,
    foo.FileCount
FROM
    Employees e
    JOIN
    (
    SELECT
       COUNT(*) AS FileCount, --OR COUNT(DISTINCT something) ?
       e2.Company, f.EmployeeID
    FROM
       Files f JOIN Employees e2 ON f.EmployeeID = e2.id
    GROUP BY
       e2.Company, f.EmployeeID
    ) foo ON e.Company = foo.Company AND e.id = foo.EmployeeID

Answer 4

There are a lot of ways to get that. If the performance is a concern, this is more optimal according to estimated execution plan costs.

SELECT
  e.FirstName,
  e.LastName,
  e.Company,
  COUNT(f.FileId)
FROM
  Employees e
  INNER JOIN Files f ON e.EmployeeID = f.EmployeeID
GROUP BY
  e.FirstName,
  e.LastName,
  e.Company

Answer 5

WITH FilesPerCompany (CompanyID, NumberOfFiles)
AS (SELECT      c.ID AS CompanyID,
                COUNT(*) AS NumberOfFiles
    FROM        Companies c
    INNER JOIN  Employees e ON  c.ID = e.CompanyID
    INNER JOIN  Files f     ON  e.ID = f.EmployeeID
    GROUP BY    c.ID
)

SELECT      e.FirstName,
            e.LastName,
            e.Company,
            COALESCE(s.NumberOfFiles, 0) AS NumberOfFilesPerCompany
FROM        Employees e
LEFT JOIN   FilesPerCompany s
        ON  s.CompanyID = e.CompanyID