Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2004
    Posts
    57

    Unanswered: Subquery Usage Question

    I have 2 tables that contain Schedule information. The first table contains Work Order's and the second table contains Task information.

    A given Work Order is composed of any combination of Tasks. Example:

    Work Order = Car
    Task 1 = Wheel Install
    Task 2 = Wheel Install
    Task 3 = Wheel Install
    Task 4 = Wheel Install

    What I'm looking for is a way to find all Work Orders that contain only 4 tasks. There's no feild in the tables that specify how many Tasks are part of a Work Order.

    I have the feeling in my gut that I need to do this query using a subquery. My only problem is that I don't know how to filter out Work Orders with less than 4 Tasks as well as those with more than 4 Tasks.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Will something like this work OK?

    select o.order_id
    from work_order o, task t
    where o.order_id = t.order_id
    group by o.order_id
    having count(t.task_id) = 4;

  3. #3
    Join Date
    Nov 2004
    Posts
    57
    Littlefoot,

    You helped me out before in the past with using virtual tablespaces. I think that I need something like that again for this query.

    I'll run my first query that looks like this:

    Code:
    select distinct work_order_id
    from papp
    group by work_order_id
    having count (papp.work_order_id) = 3
    From this query I get 4794 records. I want to then refine my query of the 4794 to look for work_order_id that are part of 3 groupings (Group_A, Group_B, Group_C)

    The query for this looks like this:

    Code:
    select distinct work_order_id
    from papp
    where papp.work_order_id in (Group_A, Group_B, Group_C)
    From this query I get 15,258 records. I'd like to run one query against the other.

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    OK, so how about including WHERE clause of your second query into the first one? Such as
    Code:
    select work_order_id
    from papp
    where papp.work_order_id in (Group_A, Group_B, Group_C)
    group by work_order_id
    having count (papp.work_order_id) = 3;
    BTW, you don't need DISTINCT when your query includes a GROUP BY because GROUP BY makes you sure you'll get distinct results.

Posting Permissions

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