I'm trying to come up with a query that will select rows from a table based on whether a specific column in the corresponding child rows of the child table are all the same value, regardless of that value. And, if the column in those child rows is the same, I'd like to get that value and return it with the parent row that is being selected.

I'm having trouble wrapping my head around this one. Hopefully my question makes sense.

Thanks

Try something like this:

PHP Code:

select * from departments d
where
( select count(job_id) from emp e where e.department_id = d.department_id) =
( select count(distinct job_id) from emp e where e.department_id = d.department_
id)
/ 


I think that's on the right track, but let me try to give an example of what I'm after.

I want to select, for example, the field State from table Employees. But given the sub-table EmployeeJobSite with a field JobSiteState, I want to replace Employee.State with EmployeeJobSite.State if all the JobSiteState's are the same for the given employee. For example:

Employees
----------
Employee : State
1 : CA
2 : NY


EmployeeJobSites
----------------
Employee : JobSiteState
1 : AZ
1 : CA
1 : NV
2 : NJ
2 : NJ

For this data set I want to return Employee #2 with the Employee.State field containing "NJ".

Probably not a great example, but maybe that helps?

I didn't quite understand: do you need ONLY employee #2, or all of them (but with special requirement for employee #2)? Such a query will return only employee #2:

Yes, I'm only looking for employee #2. The problem is I want the information contained in the Employees table (we can assume there are a number of other columns I haven't mentioned). I don't need any information from the EmployeeJobSites table, unless the JobSiteState is identical for a given employee, in which case I want to use that field instead of the Employee.State field.

I see; perhaps something like this?