Results 1 to 4 of 4

Thread: JOIN Help

  1. #1
    Join Date
    Jun 2010
    Posts
    15

    Unanswered: JOIN Help

    DB2 v9.7.100.177

    I have two tables, one contains people demographics (INFO) and the other contains actions (INFOACTIONS).

    One of the actions that can take place is "COMPLETE". I'm trying to get a list of every person who had the action COMPLETE applied to them, and what time it happened. I ran into a small problem where COMPLETE can sometimes happen twice. Unfortunately, I can't fix the issue of COMPLETE happening twice, so I must work around it and I am trying to use only the first instance of COMPLETE.

    Code:
    INFO
    ID FIRSTNAME LASTNAME
    0 JOHN SMITH
    1 JANE DOE
    2 JAMES JOHNSON
    Code:
    INFOACTIONS
    ACTIONID ID ACTIONNAME TIMESTAMP
    1 0 REGISTERED 2011-01-05 08:00:00.0
    2 0 ARRIVED 2011-01-05 09:00:00.0
    3 0 COMPLETE 2011-01-05 10:00:00.0
    4 0 COMPLETE 2011-01-05 10:30:00.0
    5 0 LEFT 2011-01-05 11:00:00.0
    6 1 REGISTERED 2011-01-05 12:00:00.0
    7 1 ARRIVED 2011-01-05 13:00:00.0
    8 1 COMPLETE 2011-01-05 14:00:00.0
    9 1 LEFT 2011-01-05 15:00:00.0
    So we have three people. Two of them have gone through the process, James Johnson did not.

    I want to get the first name, last name, and the first time they were completed.

    I tried this:

    Code:
    SELECT 
      INFO.ID, FIRSTNAME, LASTNAME, TIMESTAMP 
    FROM 
      INFO INNER JOIN 
        INFOACTIONS ON INFO.ID = INFOACTIONS.ID 
    WHERE 
      INFO.ID IN 
        (SELECT 
          INFOACTIONS.ID 
        FROM 
          INFOACTIONS 
        WHERE 
          ACTIONNAME = 'COMPLETE') 
      AND ACTIONNAME = 'COMPLETE';
    But, it returns both instances of John Smith being complete.

    Code:
    ID FIRSTNAME LASTNAME TIMESTAMP
    0 JOHN SMITH 2011-01-05 10:00:00.0
    0 JOHN SMITH 2011-01-05 10:30:00.0
    1 JANE DOE 2011-01-05 02:00:00.0
    I tried adding a bunch of "distincts" but couldn't get it to go. I have a feeling I am performing the wrong kind of join, but I'm not sure what to do to fix it.

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    distinct doesn't work because of selecting the timestamp, its different for each action. Since you want the first, go for the MIN timestamp.

    Code:
    SELECT 
      INFO.ID, FIRSTNAME, LASTNAME, min(TIMESTAMP )
        FROM INFO 
    INNER JOIN INFOACTIONS 
        ON INFO.ID = INFOACTIONS.ID 
       AND INFOACTIONS.ACTIONNAME = 'COMPLETE'
    group by INFO.ID, FIRSTNAME, LASTNAME;
    Dave

  3. #3
    Join Date
    Jun 2010
    Posts
    15
    Awesome, thanks.

    I actually have a lot more columns in the "INFO" table and now it's wanting me to add all these columns to the "GROUP BY" section. I take it this is normal behavior when adding the min() function? Obviously since the first thing I am going to be grouping by is the ID, and that's unique, nothing is actually getting grouped.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH INFO(ID, FIRSTNAME, LASTNAME) AS (
    VALUES
      (0 , 'JOHN'  , 'SMITH')
    , (1 , 'JANE'  , 'DOE')
    , (2 , 'JAMES' , 'JOHNSON')
    )
    , INFOACTIONS(ACTIONID, ID, ACTIONNAME, TIMESTAMP) AS (
    VALUES
      (1 , 0 , 'REGISTERED' , '2011-01-05 08:00:00.0')
    , (2 , 0 , 'ARRIVED'    , '2011-01-05 09:00:00.0')
    , (3 , 0 , 'COMPLETE'   , '2011-01-05 10:00:00.0')
    , (4 , 0 , 'COMPLETE'   , '2011-01-05 10:30:00.0')
    , (5 , 0 , 'LEFT'       , '2011-01-05 11:00:00.0')
    , (6 , 1 , 'REGISTERED' , '2011-01-05 12:00:00.0')
    , (7 , 1 , 'ARRIVED'    , '2011-01-05 13:00:00.0')
    , (8 , 1 , 'COMPLETE'   , '2011-01-05 14:00:00.0')
    , (9 , 1 , 'LEFT'       , '2011-01-05 15:00:00.0')
    )
    SELECT f.*
         , a.actionid , a.timestamp
     FROM  info f
     CROSS JOIN
           LATERAL
           (SELECT a.*
             FROM  infoactions a
             WHERE a.id         = f.id
               AND a.actionname = 'COMPLETE'
             ORDER BY
                   timestamp ASC
             FETCH FIRST 1 ROW ONLY
           ) a
    ;
    ------------------------------------------------------------------------------
    
    ID          FIRSTNAME LASTNAME ACTIONID    TIMESTAMP            
    ----------- --------- -------- ----------- ---------------------
              0 JOHN      SMITH              3 2011-01-05 10:00:00.0
              1 JANE      DOE                8 2011-01-05 14:00:00.0
    
      2 record(s) selected.
    See "Retrieving MAXimum row"(page 34-36) of SQL on Fire - Part 1
    http://sirdug.org/downloads/SQLonFire_1_SirDUG.pdf

    "SQL on Fire" will give you useful informations to utilize newer functionalities of DB2 SQL for performance.

Posting Permissions

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