Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2010
    Posts
    32

    Unanswered: Left Join with Conditions, but remove rows that don't match condition

    Hello,

    I've searched for a few days on this, and cannot get what I need. I have four tables, consisting of products, contract items, shiptos and accounts. all of these contain inactive records, like when we discontinue a product or a client leaves. I am tasked to produce a list of active items regardless of contracts and accounts, but containing the contracts if there are any regardless of accounts, but containing the accounts there are any.

    The problem is when i left join these tables, i can either produce a result set where only active contract items are pulled that are assigned to accounts (placing status condition in the where clause), or all contract items regardless of their active status (placing status condition in the join clause). I cannot get it to find that happy medium where it will pull only active contract items even if they're not assigned to a shipto.

    This page explains the problem perfectly:
    IBM Information Management Software for z/OS Solutions Information Center

    "Because the PARTS table can have rows that are not matched by values in the joined columns and because the PRICE column is not in the PARTS table, rows in which the PRICE value does not exceed $10.00 are included in the result of the join, but the PRICE value is set to null."

    Products:
    Code:
    item_id
    item
    item_status
    Contracts:
    Code:
    contract_id
    c_item_id
    c_shipto_id
    contract_number
    contract_status
    Shiptos:
    Code:
    shipto_id
    s_acct_id
    shipto_name
    Accounts:
    Code:
    acct_id
    acct_number
    acct_status
    Here's my current query re-written for brevity.

    Code:
    SELECT acct_number, shipto_name, contract_number, item, contract_status
    FROM products
    LEFT OUTER JOIN ( 
      contracts
      LEFT OUTER JOIN ( 
        shiptos
        INNER JOIN accounts ON acct_id=s_acct_id AND acct_status='A' 
      ) ON shipto_id=c_shipto_id AND shipto_status='A' 
    ) ON item_id=c_item_id AND contract_status='A' 
    WHERE item_status='A' 
    WITH UR
    If i un-nest the joins the result is the same, but this one runs the fastest.

    This code produces an actual result set of
    Code:
    acct1, shipto1, contractitem1, item1, A
    acct2, shipto2, contractitem2, item2, A
     null,    null, contractitem3, item3, A
     null,    null,          null, item4, null
    The red null I reddened because there actually is a value in this field in the db (a "D"), unlike the other legitimate nulls which represent actual missing shiptos or accounts.

    If the item is not actively on a contract, it shouldn't show since the contract_status is "D", and i explicitly told it to only check "A" records (A or D for active/deactive) within the left join. If i put all of these conditions in the where clause, or inner join them, then only the first 2 show.

    I need the top 3 to show. How can i achieve this?

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You are getting the red null because of you are left outer joining with a non-matching row ('A' <> 'D') (contract_status).

    If you want to eliminate those products that have no active shiptos and no active accounts, you need to add this the the WHERE clause:

    Code:
    and (acct_number is not null or shipto_name is not null or contract_number is not null)
    Andy

  3. #3
    Join Date
    Apr 2010
    Posts
    32
    ARWINNER, i've put this to the test, and it seems to have worked

    thanks!

    one thing though rather than putting the acct_name and such if null, i put the acct_status if null, since sometimes the acct name is supposed to be null like when the item is actively assigned to a contract but that contract is not assigned to any accounts.

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    It does not matter. The left outer join joins to rows, and if you try to return columns from those rows and there are no matching rows, then all of those columns from the right table will be null. So it does not matter which column you check for null, as they all will be.

    But do not check a column if it allowed to be null in its own table. That can cause problems.

    Andy

  5. #5
    Join Date
    Apr 2010
    Posts
    32
    that makes sense. thanks again.

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How about to replace first LEFT OUTER JOIN with INNER JOIN, like...

    Example 1:
    Code:
    SELECT acct_number, shipto_name, contract_number, item, contract_status
     FROM  products
    /*LEFT OUTER JOIN*/
     INNER JOIN
           contracts
     LEFT  OUTER JOIN
           shiptos
     INNER JOIN
           accounts
      ON   acct_id   = s_acct_id    AND acct_status     = 'A'
      ON   shipto_id = c_shipto_id  AND shipto_status   = 'A'
      ON   c_item_id = item_id      AND contract_status = 'A'
     WHERE item_status = 'A'
     WITH UR
    ;
    or

    Example 2: Moved an ON condition.
    Code:
    SELECT acct_number, shipto_name, contract_number, item, contract_status
     FROM  products
     INNER JOIN
           contracts
      ON   c_item_id = item_id      AND contract_status = 'A'
     LEFT  OUTER JOIN
           shiptos
     INNER JOIN
           accounts
      ON   acct_id   = s_acct_id    AND acct_status     = 'A'
      ON   shipto_id = c_shipto_id  AND shipto_status   = 'A'
     WHERE item_status = 'A'
     WITH UR
    ;
    Last edited by tonkuma; 03-11-13 at 15:23. Reason: Add second example.

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Example 3: (Changes from Example 2) Moved anoher ON condition and replaced second INNER JOIN with LEFT OUTER JOIN.
    Code:
    SELECT acct_number, shipto_name, contract_number, item, contract_status
     FROM  products
     INNER JOIN
           contracts
      ON   c_item_id = item_id      AND contract_status = 'A'
     LEFT  OUTER JOIN
           shiptos
      ON   shipto_id = c_shipto_id  AND shipto_status   = 'A'
     LEFT  OUTER JOIN
           accounts
      ON   acct_id   = s_acct_id    AND acct_status     = 'A'
     WHERE item_status = 'A'
     WITH UR
    ;

Tags for this Thread

Posting Permissions

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