Self-Join combined with Outer Join?

From: <r13l24r2_at_gmail.com>
Date: 1 Aug 2006 08:16:28 -0700
Message-ID: <1154445387.644554.309110@p79g2000cwp.googlegroups.com>

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!! Received on Tue Aug 01 2006 - 10:16:28 CDT