Outter join, with conditions on target help

From: Colin Morris <C.Morris_at_gu.edu.au>
Date: Tue, 15 Sep 1998 23:46:08 GMT
Message-ID: <35fe0dd4.118135777_at_newshost.gu.edu.au>

I'm trying to create a view on two tables, and I'm having horrid amounts of trouble combining outterjoins and conditions.

>From Table one I want every record, the key is Asset_id.

>From Table two, I want the Comments field [a long] and DTTM_Stamp [date]

I want the view to have 1 row for every Asset_id from Table one, if there is a match in table two linking to table 1 then I want that row, otherwise I need blank rows in the resultant joined table.

Further there can be more than one row with the id in table two!

If there is more than one row I don't wan't all, I want the one with the date field being the latest one...

So, if there isn't a corresponding row, just chuck a null in for the comments/ DTTM_stamp, if there's many, take the latest one.

What I think is the right sql is this, but its not working :-(

select a.asset_id, b.comments, b.dttm_stamp from ps_pi_results a,

     ps_asset_comments b
where a.asset_id = b.asset_id (+)

      and
      b.dttm_stamp = (SELECT MAX(C.DTTM_STAMP)
                        FROM PS_ASSET_COMMENTS C
                        WHERE A.ASSET_ID = C.ASSET_ID)    

/ Received on Wed Sep 16 1998 - 01:46:08 CEST