Query to Get A Parent Record When All Child Records Have The Same Condition

[ahd2008]

Good day all,

I find myself stumped unable to build a query. I have a parent table for Departments, and a child table for Employees which is related to the Departments table by Department ID. In each departments there are number of employees with different job title. However, in some departments, all the employees have the same job title.

I need to write a query to get the departments that have employees whose jobs is the same. Let's say that I have the below departments and its relevant employees. How can I write a query to bring up the Department that all of its employees have "Driller" job title which is Department ID#2?

Departments Table (Parent)
1- Dept ID#1 Dept Name= Oil
2- Dept ID#2 Dept Name=Gas
3- Dept ID#3 Dept Name=Drilling

Employees Table (Child)
Dept ID#1 EmployeeID# 12345 Job Title=Driller
Dept ID#1 EmployeeID# 67899 Job Title=Forman
Dept ID#1 EmployeeID# 33445 Job Title=Operator

Dept ID#2 EmployeeID# 44556 Job Title=Driller
Dept ID#2 EmployeeID# 22345 Job Title=Driller
Dept ID#2 EmployeeID# 90901 Job Title=Driller

Dept ID#3 EmployeeID# 50601 Job Title=Driller
Dept ID#3 EmployeeID# 90902 Job Title=Driller
Dept ID#3 EmployeeID# 30030 Job Title=Forman

[Rabbit]

Make an aggregate query that counts all employees by department and join that to an aggregate query that counts all employees by department and title. Those with the same numbers are the departments you're looking for.

[ahd2008]

A Very smart suggestion from smart guy. Thanks so much Rabbit; that's absolutely the best approach.

Regards,