sql weirdness

[Ruslan Zasukhin]

on 1/18/04 11:06 PM, Dave Parizek at dave at Parizek.com wrote:

>>>  Ok, so this works:
>>> 
>>>  SELECT ProductName,PriceThere,UsedPrice FROM Item, Venue, Product
>>>  where (Item.ItemProductPtr = Product.RecID) and (VenueItemPtr =
>>>  Item.RecID) and (PriceThere = UsedPrice)
>>> 
>>>  but if I change the last '=' to a '>' or '<' it does not work, I get
>>>  an error 616, wrong expression:
>>> 
>>> SELECT ProductName,PriceThere,UsedPrice FROM Item, Venue, Product
>>>  where (Item.ItemProductPtr = Product.RecID) and (VenueItemPtr =
>>> Item.RecID) and (PriceThere > UsedPrice)
>>> 
>>>  What am I doing wrong?
>> 
>> Hi Dave,
>> 
>> Valentina 1.x support JOIN by
>> 
>>     WHERE A.key = B.ptr
>> 
> 
> I'm confused.  I'm not trying to do a join exactly.  I have 3 tables.
> Product is joined to Item one to many.  Item is joined to Venue one
> to many.  Product and Venue are not joined except thru Item.

Okay,

where 
    (Item.ItemProductPtr = Product.RecID) and
    (VenueItemPtr = Item.RecID)
and 
    (PriceThere > UsedPrice)
    ^^^^^^^^^^^^^^^^^^^^^^^^ you can do this only in BaseObject method

 
> Now I want the Venue records where Venue.PriceThere is greater than
> Product.UsedPrice.

Argh, so they even from different tables.

> In my where clause above, the first 2 items join
> the tables, and theoretically are unnecessary since I am using object
> pointers and Valentina can figure out the relations by itself.  The
> 3rd item in the where clause is where I am attempting to limit the
> records, not do a join.
> 
> If I have 3 tables can I not do that? eg.:  If A.key = B.ptr and
> B.key=C.ptr, and I want to select out the records where C.price <
> A.price, how do I write that with Valentina?

Not works

> Or do I have to directly relate A to C?

Will not help.

> If not Valentina 1.x, will 2.0 be able to do this?

I think yes.

It is hard query for execution.

Condition PriceThere > UsedPrice must be applied to the JOIN table itself.
JOIN table is TMP table built on the fly.
It do not have indexes.
So choices are build index or iterate.



-- 
Best regards,
Ruslan Zasukhin      [ I feel the need...the need for speed ]
-------------------------------------------------------------
e-mail: ruslan at paradigmasoft.com
web: http://www.paradigmasoft.com

To subscribe to the Valentina mail list go to:
http://lists.macserve.net/mailman/listinfo/valentina
-------------------------------------------------------------