Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2006
    Posts
    3

    Unanswered: Selecting based on child row content

    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

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    Try something like this:
    PHP Code:
    select from departments d
    where
    select count(job_idfrom emp e where e.department_id d.department_id) =
    select count(distinct job_idfrom emp e where e.department_id d.department_
    id
    )



    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Sep 2006
    Posts
    3
    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?
    Last edited by matt7340; 09-21-06 at 01:09.

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    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:
    Code:
    SELECT es.employee
    FROM EMPLOYEEJOBSITES es
    GROUP BY es.employee
    HAVING COUNT(DISTINCT jobsitestate) = 1;

  5. #5
    Join Date
    Sep 2006
    Posts
    3
    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.

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I see; perhaps something like this?
    Code:
    SELECT DISTINCT e.emp, x.job_state, e.other_column_1, e.other_column_2
               FROM (SELECT es.emp, es.job_state
                       FROM emp_job_sites es
                      WHERE 1 = (SELECT COUNT (DISTINCT es1.job_state)
                                 FROM emp_job_sites es1
                                WHERE es1.emp = es.emp)) x,
                     employees e
              WHERE e.emp = x.emp;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •