Sub-Query Problem

Department(DepartID,DepName)

Employees(Name,DepartID)

What i need is the Count of Employees in the Department with DepName开发者_开发知识库.

---

If you are using SQL Server version 2005 or above, here is another possible way of getting employees count by department. .

SELECT      DPT.DepName
        ,   EMP.EmpCount
FROM        dbo.Department DPT
CROSS APPLY (
                SELECT  COUNT(EMP.DepartId) AS EmpCount
                FROM    dbo.Employees EMP
                WHERE   EMP.DepartId = DPT.DepartId
            ) EMP           
ORDER BY    DPT.DepName

Hope that helps.

Sample test query output:

---

I'd use an outer join rather than a subquery.

SELECT d.DepName, COUNT(e.Name)
FROM Department d
LEFT JOIN Employees e ON e.DepartID = d.DepartID
GROUP BY d.DepartID, d.DepName

---

SELECT d.DepName, COUNT(e.Name)
    FROM Department d
        LEFT JOIN Employees e
            ON d.DepartID = e.DepartID
    GROUP BY d.DepName

---

No need for a subquery.

SELECT dep.DepName, COUNT(emp.Name)
FROM DepName dep
    LEFT OUTER JOIN Employees emp ON dep.DepartID = emp.DepartID
GROUP BY dep.DepName

---

SELECT COUNT(DISTINCT Name) FROM  
 Department AS d, Employees AS e   
WHERE d.DepartID=e.DepartID AND d.DepName = '$thename'

---

And to avoid using a group by and save you a Sort operation in the queryplan:

SELECT 
    Department.DepName, 
    (SELECT COUNT(*)
    FROM Employees 
    WHERE Employees.DepartID = Department.DepartID)
FROM 
    Department