JOIN Help

Pryach · #1 (**permalink**) 01-05-11, 19:18

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.

dav1mo · #2 (**permalink**) 01-05-11, 19:56

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

Pryach · #3 (**permalink**) 01-05-11, 23:25

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.

tonkuma · #4 (**permalink**) 01-09-11, 17:45

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech