Re: Self-Join combined with Outer Join?

From: <fitzjarrell_at_cox.net>
Date: 1 Aug 2006 08:49:54 -0700
Message-ID: <1154447393.992183.214040@m79g2000cwm.googlegroups.com>

r13l24r2_at_gmail.com wrote:
> I guess this is probably a simple problem, but I can't for the life of
> me figure it out. Say I have the following table:
>
> job_id batch_id action date
> ------------ ------------- ------------- --------
> 1 1 sent 1/1/01
> 2 1 received 1/2/01
> 3 2 sent 1/3/01
> 4 2 received 1/4/01
> 5 3 sent 1/5/01
>
>
> I want to write a query that will display as follows:
>
> batch_id sent received
> ------------ ------------ ------------
> 1 1/1/01 1/2/01
> 2 1/3/01 1/4/01
> 3 1/5/01
>
> I can self-join the table so that it gives me a query displaying
> batch_id 1 and 2 no problem. I just can't make it display rows for
> which there is no received action.
>
> Any thoughts?? I'm desperate!!

Given your data:

    JOB_ID BATCH_ID ACTION PROC_DATE

---------- ---------- ------------ ---------
         1          1 sent         01-JAN-01
         2          1 received     02-JAN-01
         3          2 sent         03-JAN-01
         4          2 received     04-JAN-01
         5          3 sent         05-JAN-01

the following query returns the desired result set:

  1 with col_generator as (
  2 select j1.batch_id,

  3         decode(j1.action, 'sent', j1.proc_date) sent,
  4         decode(j2.action,'received', j2.proc_date) received

  5 from jobtest j1, jobtest j2
  6 where j2.batch_id (+)= j1.batch_id
  7 )
  8 select batch_id, max(sent) sent, max(received) received   9 from col_generator
 10 group by batch_id
 11* order by 1
SQL> /   BATCH_ID SENT RECEIVED

---------- --------- ---------
         1 01-JAN-01 02-JAN-01
         2 03-JAN-01 04-JAN-01
         3 05-JAN-01

SQL> Possibly Jonathan Lewis can improve on this, but it does work and, if the only two values for ACTION are 'sent' and 'received' you should have your answer.

David Fitzjarrell Received on Tue Aug 01 2006 - 10:49:54 CDT