By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,454 Members | 2,629 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,454 IT Pros & Developers. It's quick & easy.

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

P: 68
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
Jun 26 '12 #1
Share this Question
Share on Google+
2 Replies


Rabbit
Expert Mod 10K+
P: 12,315
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.
Jun 26 '12 #2

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

Regards,
Jun 27 '12 #3

Post your reply

Sign in to post your reply or Sign up for a free account.