complex sql query: problem with "or"

[Ruslan Zasukhin]

On 10/1/04 1:45 AM, "nexbyte gmbh - Simon Anderegg" <s.anderegg at nexbyte.com>
wrote:

Hi Sam,

Thank you very much for good words.
Your application looks very nice!

You are right -- good database backend allow to developers do miracles.
I am always impressed what cool things developers can do with Valentina.
I'd couldn't!  :-)

> hi ruslan
> 
> time to say that valentina db is the greatest xtra for director i know!
> valentina was the perfect choice to develop the application containings
> really complex sql queries and more than 10 related tables.
> 
> - more than 30 tables
> - material table, more than 10000 entries
> - dynamic db search (requery db after every key pressing, only possible
> with the power of valentina)
> - 3 different languages (handled in valentina)
> - calculates machining tool data in different units
> - creating pdf files on the fly (db content -> pdf)
> 
> it was a challenge, but really funny.
> ...and thanks again for your fantastic support!
> 
> sam


> Ruslan Zasukhin wrote:
> 
>> On 8/27/04 1:33 AM, "s.anderegg at nexbyte.com" <s.anderegg at nexbyte.com> wrote:
>> 
>> 
>>> Name:      sam
>>> 
>>> Question regarding: sql
>>> 
>>> hi
>>> 
>>> i have to reengineering a access project to macromedia director and had
>>> buyed the valentina db (current version). everyting works fine exept the sql
>>> queries containing &#34;or&#34; arguments:
>>> 
>>> SELECT t1.recID, t1.name, t2.minval t2.maxval FROM t1, t2 WHERE t2.minval
>>> &#60;= 50 AND t2.maxval &#62;= 50 AND (t1.recID = t2.ptr_possible_id_1 OR
>>> t1.recID = t2.ptr_possible_id_2)
>>> 
>>> on every other database (exept from valentina) my example query works fine.
>>> the same query without &#34;OR...&#34; does it also.
>>> 
>>> in your usergroup you had described several solutions to solve the problems
>>> with &#34;or&#34;, but every test failed with my complex sql query.
>>> (compareing 2 times 2 variable values from 2 tables...)
>>> 
>>> - do you have a solution (one sql statement) for my problem? - i think (like
>>> other users in the ug) this is a very basic function of each sql database.
>>> do you have a patch, a bugfix or a newer (and stable) version of valentina
>>> where the &#34;or&#34; argument are implemented correctly?
>>> 
>>> greetings from switzerland (by night)
>>> sam
>> 
>> 
>> Hi Sam,
>> 
>> Valentina 2.0 which we finish now must work better with such quires.
>> 
>> Your query even differ from others guys on list.
>> You use OR between "link conditions"!
>> 
>> I think you can try this form.
>> 
>> Note, that you do join
>> 
>>     t1.recID = t2.ptr_possible_id_1 OR
>>     t1.recID = t2.ptr_possible_id_2
>> 
>> You want all records from T2 which have NON-zero pointer
>>     ptr_possible_id_1  or ptr_possible_id_2
>> 
>> 
>> So
>> 
>> SELECT t1.recID, t1.name, t2.minval t2.maxval
>> FROM t1, t2
>> WHERE T1.Recid IN (
>>                     SELECT ???
>>                     FROM T2
>>                     WHERE t2.minval &#60;= 50 AND t2.maxval &#62;= 50 and
>>                       (ptr_possible_id_1 <> 0 or ptr_possible_id_1 <> 0)
>>                   )
>> 
>> 
>> Problem is -- what to SELECT in the second subquery.
>> 
>> Probably we can use here BaseObject method with formula
>> 
>>     if( ptr_possible_id_1 <> 0, ptr_possible_id_1, ptr_possible_id_2 )
>> 
>> As result in subquery we must get table with one column which contain values
>> of T1.RecID
>> 
>> Then we apply IN to this values.

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