0

Hi, I have a question regarding outer joins. Suppose I have the following 2 tables each having 2 columns.
Employee: EmpId, DeptId
Department: DeptId, DeptId

EmpId is primary key in Employee and DeptId is primary key in Department. Also, Employee.DeptId is foreign key referencing Deparment.DeptId.

My first question:
1. Suppose I want all the department ids and the number of employees in them (even if there are 0 employees), is the below query correct:

Select D.DeptId, Count(*) 
from Department D LEFT OUTER JOIN Employee E
ON D.DeptId = E.DeptId
GROUPBY D.DeptId;

2. To find all the department ids having 0 employees in them, is the following correct?

Select D.DeptId 
from Department D LEFT OUTER JOIN Employee E
ON D.DeptId = E.DeptId
GROUPBY D.DeptId
HAVING COUNT(*) = 0;

Thanks for your time.

2
Contributors
1
Reply
2
Views
7 Years
Discussion Span
Last Post by urtrivedi
This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.