CODEWITHSUNDEEP
sqlt-sqljoin
dincerm
dincerm

Reputation: 1475

What kind of JOIN statement do I need?

I have two tables PROCESS and STEP and some data inside as show below. (They have 1-N relationship.)

PROCESS ->

ID START_TIME STARTED_BY STATUS
1  31/08/2012 User1      FINISHED
2  31/08/2012 User2      FINISHED
3  05/09/2012 User3      ACTIVE

STEP ->

ID PROCESS_ID START_TIME END_TIME   STATUS
1  1          31/08/2012 02/09/2012 FINISHED  
2  1          02/09/2012 03/09/2012 FINISHED    
3  1          03/09/2012 10/09/2012 FINISHED  
4  2          31/08/2012 04/09/2012 FINISHED  
5  2          04/09/2012 06/09/2012 FINISHED  
6  2          06/09/2012 09/09/2012 FINISHED  
7  3          05/09/2012 06/09/2012 FINISHED    
8  3          06/09/2012 NULL       ACTIVE

What I need is a JOIN, which will give me Start and End times of Finished processes like below:

PROCESS_ID START_TIME END_TIME  
1          31/08/2012 10/09/2012  
2          31/08/2012 09/09/2012

What kind of JOIN statement do I need to write to accomplish this?

Upvotes: 0

Views: 65

Answers (2)

gbn
gbn

Reputation: 432611

To avoid pulling in PROCESS_ID 3, you need to aggregate and filter first

The HAVING clause will ensure you ignore all rows for PROCESS_ID 3 in the STEP table. A simple WHERE END_TIME IS NOT NULL or WHERE .. 'FINISHED' won't do it. Neither will aggregating after the JOIN

SELECT
   P.PROCESS_ID,
   S.StartTime, S.EndTime
FROM
   PROCESS P
   JOIN
   (
   SELECT
      PROCESS_ID,
      MIN(START_TIME) AS StartTime,
      MAX(END_TIME) AS EndTime
   FROM
      STEP
   GROUP BY
      PROCESS_ID
   HAVING
      COUNT(*) = COUNT(END_TIME) -- only where we have no END_TIME nulls
   ) S ON P.PROCESS_ID = S.PROCESS_ID

There are other options with a NOT EXISTS to ignore ACTIVE rows, for example, but I'm using just END_TIME and NULLs here to ignore STEP rows

Upvotes: 2

Darren
Darren

Reputation: 70776

Looks like you need an INNER JOIN and use the MIN and MAX functions.

SELECT PROCESS.PROCESS_ID, MIN(START_TIME), MAX(END_TIME)
FROM PROCESS
INNER JOIN STEP ON STEP.PROCESS_ID = PROCESS.PROCESS_ID
GROUP BY PROCESS.PROCESS_ID

Upvotes: 1

Related Questions