Is there a way to select records based on comparing field values in two related tables?

[Steven Ronald]

Ruslan,

I have two tables A and B in a database. Table A
includes an Object_ptr field that contains the RecID
of the one record in Table B with the same value of
foo1:

A
foo1     foo2   foo3        timestamp        B_ptr
43          3        34       july 12 2005        (1)
23          4        45       july 25 2005        (2)
43          1        10       sep 20 2005        (1)


B
foo1     frac2      tpayment          (RecID)
43        0.22      july 20 2005         (1)
23        0.33      jan 12 2005          (2)

I want to retrieve the value of foo2 for all records
in A with foo1=43 and a timestamp less than the
corresponding value of tpayment from Table 2. In the
above example, there is one result foo2=3.

I could do this with two sql statements:

cursor=select tpayment from B where foo1=43
tpayment=valgetfield(cursor)
cursor=select foo2 from A where timestamp<tpayment

but is there a way to do this with a single sql
statement?

Best Regards,
Steven


Send instant messages to your online friends http://au.messenger.yahoo.com