Member Avatar for dumbncool

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
  • 90 Views
  • 7 Hours Discussion Span
  • Latest Post Latest Post by urtrivedi
Member Avatar for urtrivedi

Its perfect.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.