Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Apr 2010
    Posts
    32

    Unanswered: Left Join, Null fields, and Conditional fields

    Hello,

    There are a lot of similar topics on this but none seem to address left joining a table based on fields conditional of specific values that I can find. I'm not sure how to get what I need.

    In English, I need a list of users who have "active" shiptos assigned to their profile and anyone that doesn't have any and needs one. It's the status of the shiptos that are spinning me in a loop as some of them have assigned inactive shiptos.

    Here's my query:
    Code:
    WITH tUsers (u_id, u_name) AS ( 
      VALUES 
       (11, 'Bob') 
      ,(12, 'Steve') 
      ,(13, 'Joe') 
    ), tUsersShiptos (us_id, us_u_id, us_s_id) AS ( 
      VALUES 
       (21, 11, 31)
      ,(22, 11, 32) 
      ,(23, 12, 32) 
    ), tShiptos (s_id, s_name, s_status) AS ( 
      VALUES 
       (31, 'st1', 'A') --Active
      ,(32, 'st2', 'D') --Deactive
    ) (
      SELECT u_name, s_name 
      FROM tUsers 
      LEFT OUTER JOIN tUsersShiptos ON u_id=us_u_id 
      LEFT OUTER JOIN tShiptos ON us_s_id=s_id AND s_status='A' 
    ) WITH UR
    And Here's the result:
    Code:
    Bob	st1
    Bob	
    Steve	
    Joe
    I need to get rid of the 2nd Bob as he has an active shipto so the inactive shipto row doesn't need to show. I tried this query:
    Code:
      SELECT u_name, s_name
      FROM tUsers 
      LEFT OUTER JOIN tUsersShiptos ON u_id=us_u_id 
      LEFT OUTER JOIN tShiptos ON us_s_id=s_id AND s_status='A' 
      WHERE s_name IS NOT NULL
    Which left me with this result:
    Code:
    Bob	st1
    I still need Steve and Joe to show because they have either no shiptos or all inactive shiptos. It has to show that they need an active one. I want to avoid simply running two queries and UNIONing them together because I don't want to query the same tables more than once. So I tried this query:
    Code:
      SELECT u_name, s_name, COUNT(s_id) OVER(PARTITION BY u_id) AS scount 
      FROM tUsers 
      LEFT OUTER JOIN tUsersShiptos ON u_id=us_u_id 
      LEFT OUTER JOIN tShiptos ON us_s_id=s_id AND s_status='A' 
      WHERE (s_name IS NOT NULL OR scount=0)
    But it errs on "scount" in the WHERE clause.

    The expected result is this:
    Code:
    Bob	st1
    Steve
    Joe
    How can i achieve the expected result? Any help is appreciated. thanks,

  2. #2
    Join Date
    Apr 2010
    Posts
    32
    Actually, this works here..

    Code:
      SELECT u_name, s_name 
      FROM (
        SELECT u_name, s_name, COUNT(s_id) OVER(PARTITION BY u_id) AS scount 
        FROM tUsers 
        LEFT OUTER JOIN tUsersShiptos ON u_id=us_u_id 
        LEFT OUTER JOIN tShiptos ON us_s_id=s_id AND s_status='A' 
      ) 
      WHERE (s_name IS NOT NULL OR scount=0)
    Sometimes things come to me by the act of asking others, even before I hear back. So in that case, you came through thanks

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    other two ways.

    Code:
    SELECT u_name, s_name 
      FROM (
        SELECT u_name, s_name
             , ROW_NUMBER() OVER(PARTITION BY u_id
                                     ORDER BY s_name ASC) AS rn 
        FROM tUsers 
        LEFT OUTER JOIN tUsersShiptos ON u_id=us_u_id 
        LEFT OUTER JOIN tShiptos ON us_s_id=s_id AND s_status='A' 
      ) 
      WHERE rn = 1

    Code:
      SELECT u_name, MAX(s_name) AS s_name 
      FROM tUsers 
      LEFT OUTER JOIN tUsersShiptos ON u_id=us_u_id 
      LEFT OUTER JOIN tShiptos ON us_s_id=s_id AND s_status='A' 
      GROUP BY
            u_name

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Another example.

    Note: Generally speaking, INNER JOIN may be more efficient than OUTER JOIN.
    Code:
    SELECT u_name, MAX(s_name) AS s_name
     FROM  tUsers
     LEFT  OUTER JOIN
           tUsersShiptos
     INNER JOIN
           tShiptos
      ON   us_s_id = s_id AND s_status = 'A'
      ON   us_u_id = u_id
     GROUP BY
           u_name
    ;

  5. #5
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by tonkuma View Post
    Another example.

    Note: Generally speaking, INNER JOIN may be more efficient than OUTER JOIN.
    Code:
    SELECT u_name, MAX(s_name) AS s_name
     FROM  tUsers
     LEFT  OUTER JOIN
           tUsersShiptos
     INNER JOIN
           tShiptos
      ON   us_s_id = s_id AND s_status = 'A'
      ON   us_u_id = u_id
     GROUP BY
           u_name
    ;
    Hey! The keyword "ON" twice. Is that a typo or did I miss something?
    Somewhere between " too small" and " too large" lies the size that is just right.
    - Scott Hayes

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    It's not typo.

    If added parentheses, it might be easier to understand.
    Code:
    SELECT u_name, MAX(s_name) AS s_name
     FROM
           tUsers
     LEFT  OUTER JOIN
      (
             tUsersShiptos
       INNER JOIN
             tShiptos
        ON   us_s_id = s_id AND s_status = 'A'
      )
      ON   us_u_id = u_id
     GROUP BY
           u_name
    ;

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Also see this description
    If a join-operator is not specified, INNER is implicit. The order in which multiple joins are performed can affect the result. Joins can be nested within other joins.
    The order of processing for joins is generally from left to right, but based on the position of the required join-condition. Parentheses are recommended to make the order of nested joins more readable. For example:
    Code:
       TB1 LEFT JOIN TB2 ON TB1.C1=TB2.C1
         RIGHT JOIN TB3 LEFT JOIN TB4 ON TB3.C1=TB4.C1
           ON TB1.C1=TB3.C1
    is the same as:
    Code:
       (TB1 LEFT JOIN TB2 ON TB1.C1=TB2.C1)
         RIGHT JOIN (TB3 LEFT JOIN TB4 ON TB3.C1=TB4.C1)
           ON TB1.C1=TB3.C1A
    in joined-table of IBM Knowledge Center

  8. #8
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by tonkuma View Post
    It's not typo.If added parentheses, it might be easier to understand.
    I see, thank you. I am the 'kind of guy' who really needs those parantheses to understand the code.
    Somewhere between " too small" and " too large" lies the size that is just right.
    - Scott Hayes

  9. #9
    Join Date
    Apr 2010
    Posts
    32
    A lot of good ideas. thanks guys. I think some may only work if a single shipto is allowed. I forgot to mention that there is no limit to the amount of shiptos that can be assigned to a user. This is a M to M relationship.

    Quote Originally Posted by tonkuma View Post
    Another example.

    Note: Generally speaking, INNER JOIN may be more efficient than OUTER JOIN.
    Code:
    SELECT u_name, MAX(s_name) AS s_name
     FROM  tUsers
     LEFT  OUTER JOIN
           tUsersShiptos
     INNER JOIN
           tShiptos
      ON   us_s_id = s_id AND s_status = 'A'
      ON   us_u_id = u_id
     GROUP BY  u_name
    ;
    This is interesting. I always write it with the ON immediately following the JOIN.

    Code:
      SELECT u_name, s_name 
      FROM tUsers 
      LEFT OUTER JOIN tUsersShiptos ON u_id=us_u_id
      INNER JOIN tShiptos ON us_s_id=s_id AND s_status='A' ON u_id=us_u_id
    When I move the red part to the green part as in your example, then it works. This looks the most efficient; however, when I run an explain on it the timeron score was several times higher. I'm not sure why. I haven't quite determined the link between timeron value and performance though because some queries I have tweaked that pull the same data have higher timeron value and runs in half the time.

    thanks again.

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by DonQuixote View Post
    A lot of good ideas. thanks guys. I think some may only work if a single shipto is allowed. I forgot to mention that there is no limit to the amount of shiptos that can be assigned to a user. This is a M to M relationship.
    Do you mean multiple Active shiptos can be assigned to a user?

  11. #11
    Join Date
    Apr 2010
    Posts
    32
    Quote Originally Posted by tonkuma View Post
    Do you mean multiple Active shiptos can be assigned to a user?
    Yea, multiple active shiptos. Users can chose where to ship their products, and some require thousands of shiptos.

  12. #12
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    And multiple Deactive shiptos can be assigned to a user?

    Like this...
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH tUsers (u_id, u_name) AS ( 
      VALUES 
       (11, 'Bob') 
      ,(12, 'Steve') 
      ,(13, 'Joe')
      ,(14, 'Jane')
    ), tUsersShiptos (us_id, us_u_id, us_s_id) AS ( 
      VALUES 
       (21, 11, 31)
      ,(22, 11, 32)
      ,(23, 12, 32)
      ,(24, 12, 34)
      ,(25, 11, 33)
      ,(26, 14, 31)
      ,(27, 14, 32)
      ,(28, 14, 34)
      ,(29, 14, 35)
    ), tShiptos (s_id, s_name, s_status) AS ( 
      VALUES 
       (31, 'st1', 'A') /*Active*/
      ,(32, 'st2', 'D') /*Deactive*/
      ,(33, 'st3', 'A') /*Active*/
      ,(34, 'st4', 'D') /*Deactive*/
      ,(35, 'st5', 'A') /*Active*/
    )
      SELECT u_name, s_name
      FROM tUsers 
      LEFT OUTER JOIN tUsersShiptos ON u_id=us_u_id 
      LEFT OUTER JOIN tShiptos ON us_s_id=s_id AND s_status='A' 
      ORDER BY
            u_name
    ;
    Result:
    Code:
    ------------------------------------------------------------------------------
    
    U_NAME S_NAME
    ------ ------
    Bob    st1   
    Bob    -     
    Bob    st3   
    Jane   st1   
    Jane   -     
    Jane   -     
    Jane   st5   
    Joe    -     
    Steve  -     
    Steve  -     
    
      10 record(s) selected.
    If so, your query
    Code:
    SELECT u_name, s_name 
      FROM (
        SELECT u_name, s_name, COUNT(s_id) OVER(PARTITION BY u_id) AS scount 
        FROM tUsers 
        LEFT OUTER JOIN tUsersShiptos ON u_id=us_u_id 
        LEFT OUTER JOIN tShiptos ON us_s_id=s_id AND s_status='A' 
      ) 
      WHERE (s_name IS NOT NULL OR scount=0)
    ;
    returned
    Code:
    U_NAME S_NAME
    ------ ------
    Bob    st1   
    Bob    st3   
    Steve  -     
    Steve  -     
    Joe    -     
    Jane   st1   
    Jane   st5   
    
      7 record(s) selected.
    Is the result what you want?

  13. #13
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    also, for those really after performance, you can sometimes get a different access path depending on where you place your ON clause in some of these situations. Your data results will be the same, but the placement can make a dramatic difference in your performance. I, believe, the second will most times perform better than the first.

    Code:
    select cols
       from table1 a
    left outer join table2 b
        left outer join table3 c
            on b.key = c.key
       on a.col1 = b.col2
    Code:
    select cols
       from table1 a
    left outer join table2 b
       on a.col1 = b.col2
        left outer join table3 c
            on b.key = c.key
    Dave

  14. #14
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    To remove duplicated Steve from my last sample data,
    I have tow ideas.

    Example 1: Modify your query. First, join tUsersShiptos and tShiptos. Then, join tUsers.
    Code:
    SELECT u_name, s_name
     FROM  (SELECT u_name, s_name
                 , COUNT(s_id) OVER(PARTITION BY u_id) AS scount
             FROM  tUsers
             LEFT  OUTER JOIN
                   tUsersShiptos
             INNER JOIN
                   tShiptos
              ON   us_s_id = s_id AND s_status = 'A' 
              ON   u_id = us_u_id
           ) 
     WHERE s_name IS NOT NULL
      OR   scount = 0
    ;

    Example 2: Add "OR s_name IS NOT NULL" to my first query.
    Code:
    SELECT u_name, s_name 
      FROM (
        SELECT u_name, s_name
             , ROW_NUMBER() OVER(PARTITION BY u_id
                                     ORDER BY s_name ASC) AS rn 
        FROM tUsers 
        LEFT OUTER JOIN tUsersShiptos ON u_id = us_u_id 
        LEFT OUTER JOIN tShiptos ON us_s_id = s_id AND s_status = 'A' 
      )
      WHERE rn = 1
       OR   s_name IS NOT NULL
    ;

  15. #15
    Join Date
    Apr 2010
    Posts
    32
    oh wow, you're right. Good catch. I could either throw a DISTINCT or use this below as suggested earlier. I find DISTINCT to be lazy.

    Code:
      SELECT u_name, s_name 
      FROM tUsers 
      LEFT OUTER JOIN tUsersShiptos 
      INNER JOIN tShiptos ON us_s_id=s_id AND s_status='A' ON u_id=us_u_id
    Unfortunately I can't join users after. This is just a piece of a larger query and the rest become costly when I try. Standalone though its a good suggestion.

Posting Permissions

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