2) The LEFT OUTER JOIN gievs you the record from feedinv w/o matching records in ratpric but they are filtered aout by AND fi_dlvdat - rp_date < 7 in the WHERE clause. Try to move it to the JOIN conditions.

>
>I'm having trouble getting the desired results for an SQL statement.
>
>I have 2 tables. FEEDINV has a bunch of records of invoices, each with a delivery date. For each record in FEEDINV, I want a matching record in RATPRIC, where the date in RATPRIC is equal to or less than, BUT I only want the CLOSEST record in RATPRIC AND only if is within 7 days. (Kind of like SET NEAR ON, SEEK). ALSO, I only want a match if FEEDINV.fi_cpprim = RATPRIC.rp_cpprim.
>
>The attached code doesn't work because:
>
>1) It will give me ALL records in RATPRIC that are within 7 days and I only want the CLOSEST.
>2) For some reason, it doesn't give me FEEDINV records if the cpprim field cannot be found in RATPRIC. I thought that it shoud give you NULL values if there is no match on a RIGHT OUTER JOIN.
>
>Thanks,
>
>Yossi
>
>

>SET DELETED on
>
>SELECT fi_flcode, fi_cpprim, fi_weight, fi_dlvdat, rp_rtprim, rp_date, rp_cpprim ;
>FROM feedinv ;
>left OUTER JOIN ratpric ;
>ON fi_rtprim = rp_rtprim ;
>AND fi_cpprim = rp_cpprim ;
>AND fi_dlvdat > rp_date ;
>WHERE fi_flcode = 'BAA12062'  ;
>AND fi_dlvdat - rp_date < 7
>