Outer join once more...

From: vidas <vidasm_at_yahoo.com>
Date: Sat, 21 Jul 2001 21:56:21 GMT
Message-ID: <cdbd2dd.0106202326.21447c54_at_posting.google.com>

Thanks for the comments.
Some explanations to my message.
I have table:
*) store with columns acop, codadest, cod, sdate *) invsell with - opname, destname, type, price, validdate

what I need is to get all rows from the store with the latest
(valliddate) price from invsell (if it is possible to find one) with
respect to sdate (from store) - of course - it should be that store.acop=invsell.opname, etc.

My select was:

Select t1.sdate, t1.acop, t1.bcop, t1.min, t2.price from store t1, invsell t2
where
t1.acop =t2.opname (+)

and t1.codadest =t2.destname (+)
and t1.cod =t2.type (+)
and t2.validdate =

(select max(validdate) from invsell t4

where

t2.opname=t4.opname and 
t2.destname=t4.destname and
t2.networktype=t4.type and
t4.validdate < t1.sdate)

thanks,
vidas Received on Sat Jul 21 2001 - 23:56:21 CEST