subqueery to count number of dependants

  • Thread starter Thread starter mcourter
  • Start date Start date
M

mcourter

i have an employee table and a dependant table. i need to create a
query that will list employee info and the number of dependants for
each employee, but no dependant info. i think i need a subquery, but
not sure how to crack this one. count function?
tia,
mcnewsxp
 
i used this:
SELECT Employee.*,
(SELECT COUNT(*) FROM Dependent WHERE Employee.EmployeeID =
Dependent.EmployeeID) AS EmpDependants
FROM Employee ;

now i need to select only those who have dependants greater than 1.
?
 
i used this:
SELECT Employee.*,
(SELECT COUNT(*) FROM Dependent WHERE Employee.EmployeeID =
Dependent.EmployeeID) AS EmpDependants
FROM Employee ;

now i need to select only those who have dependants greater than 1.
?
Click to expand...

SELECT Employee.*,
(SELECT COUNT(*) FROM Dependent WHERE Employee.EmployeeID =
Dependent.EmployeeID) AS EmpDependants
FROM Employee
WHERE EmpDependens > 1;


John W. Vinson[MVP]
 
Back
Top