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

[Ruslan Zasukhin]

On 11/23/06 6:33 AM, "Steven Ronald" <stevenrt39 at yahoo.com.au> wrote:

> 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?

First of all.

* WHY you have foo1 in the table B ?

    this is DUPLICATION of data. Right?
    this is not good.
    You do not need that!

> 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
> frac2      tpayment          (RecID)
> 0.22      july 20 2005         (1)
> 0.33      jan 12 2005          (2)

Query you need is:

SELECT A.foo2
FROM A join B on A.B_ptr = B.recid
WHERE timestamp < tpayment


Actually this can be done in more short form for Valentina

SELECT A.foo2
FROM A join B
WHERE timestamp < tpayment



-- 
Best regards,

Ruslan Zasukhin
VP Engineering and New Technology
Paradigma Software, Inc

Valentina - Joining Worlds of Information
http://www.paradigmasoft.com

[I feel the need: the need for speed]