[V4MD] OR query results

[Ruslan Zasukhin]

on 12/19/03 5:58 PM, Martin Kloss at martin.kloss at gmx.de wrote:

Hi Martin,
 
> I get weird results from a query and I don't understand why.
> Here's the setup (simplified for this example):
> 
> table a: title, type_ptr
> table b: type_name
> 
> a simple query works fine (5 records):
> SELECT title, type_name FROM a, b WHERE title LIKE '(?i)foo' OR title LIKE
> '(?i)bar'
> 
> then I want to exclude some records, works also (3 records):
> SELECT title, type_name FROM a, b WHERE (title LIKE '(?i)foo' OR title LIKE
> '(?i)bar') AND (title NOT LIKE  '(?i)test')
> 
> now I want to select only certain types (309 records):
> SELECT title, type_name FROM a, b WHERE (type_name = 'red' OR type_name =
> 'blue') AND (title LIKE '(?i)foo' OR title LIKE '(?i)bar') AND (title NOT
> LIKE  '(?i)test')
> 
> what happens here?
> it seems like the join can not compare fields in different tables or
> something.

Yes, OR not works if fields are from different tables.
And there is no easy workaround.

> I could try to rewrite the query to use the "type_ptr" field in the same
> table, would that help?

If you get all fields from one table, then yes.

> SELECT title, type_name FROM a, b WHERE (type_ptr = '1' OR type_ptr = '2')
> AND (title LIKE '(?i)foo' OR title LIKE '(?i)bar') AND (title NOT
> LIKE  '(?i)test')

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