Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2012
    Posts
    23

    Unanswered: complex (to me) select not working

    SELECT PART_ID, AREA_ID, QTY
    FROM PART_AREA PA
    INNER JOIN PART P ON PA.PART_ID = P.ID
    WHERE (QTY < 100 AND AREA_ID IN('07','08')) OR (QTY > 0 AND AREA_ID LIKE 'QZ%');

    This query above pulls every area_id that has 07,08, or QZ* (and the other criteria). I need to show if the PART ids are the same, pair 07 & 08 with a QZ* and only show those area_id's. So like this:

    Code:
    part id -               area_id
    123                       07
    123                       QZ222
    567                       08
    567                       QZ999
    567                       QZ33
    in other words: pair each 07 or 08 with a matching QZ*. Also 07 & 08 have to be QTY < 100, QZ* must be QTY > 0
    I've been playing around with nested SELECT and DISTINCT, but no success.

    TIA Penny
    Last edited by Pat Phelan; 12-21-12 at 11:13. Reason: Added code tags to make data more readable

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd start with something like:
    Code:
    SELECT PART_ID, AREA_ID, QTY 
       FROM PART AS p
       INNER JOIN PART_AREA AS a
          ON (a.PART_ID = p.ID)
       INNER JOIN PART_AREA AS b
          ON (b.PART_ID = p.ID)
       WHERE  a.QTY < 100
          AND a.AREA_ID IN('07','08')
          AND b.QTY > 0 
          AND b.AREA_ID LIKE 'QZ%';
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Dec 2012
    Posts
    23
    Hi Pat,

    I tried this and it's not working.

    None of the QZ area_id is showing up. Only 07 & 08.

  4. #4
    Join Date
    Dec 2012
    Posts
    23
    ON FURTHER review i see that
    A.area_id has all of the 07 & 08's
    and
    B. area_id has all of the QZ's

    How do I combine them to be viewed in 1 column?

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Maybe:
    Code:
    SELECT a.PART_ID
    ,  a.QTY + b.QTY AS total
    ,  a.AREA_ID, a.QTY 
    ,  b.AREA_ID, b.QTY 
       FROM PART AS p
       INNER JOIN PART_AREA AS a
          ON (a.PART_ID = p.ID)
       INNER JOIN PART_AREA AS b
          ON (b.PART_ID = p.ID)
       WHERE  a.QTY < 100
          AND a.AREA_ID IN('07','08')
          AND b.QTY > 0 
          AND b.AREA_ID LIKE 'QZ%';
    If not, show me the result set you want given the data that you posted above.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Dec 2012
    Posts
    23
    Thank you Pat.

    I let them print to 2 columns showing that the parts match and I think it looks fine - I'll see what the boss says though.

    Your original solution does work.

Posting Permissions

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