Newbie Question : Related tables and OR condition

[Ruslan Zasukhin]

on 9/19/03 8:34 AM, Greg DeVore at gdevore at mac.com wrote:

> I actually have several tables.  But to simplify what I am doing let's
> just deal with two.
> 
> Table 1 - Music_LIbrary
> Title
> Comments
> Genre_Ptr
> 
> Table 2 - Genre
> Genre
> 
> First SQL:
> SELECT Title, Comments, Genre FROM Music_Library, Genre WHERE Title
> LIKE 'the' no_case
> 
> This returns 1013 records.
> 
> Second SQL:
> SELECT Title, Comments, Genre FROM Music_Library, Genre WHERE Title
> LIKE 'the' no_case OR Comments LIKE 'the' no_case
> 
> This returns 1045 records so it is expanding my search, as an OR
> statement should.  So far I am really only querying the Music_Library
> Table
> 
> 3rd SQL:
> SELECT Title, Comments, Genre FROM Music_Library, Genre WHERE Title
> LIKE 'the' no_case OR Comments LIKE 'the' no_case OR Genre LIKE 'the'
> no_case
> 
> Now when I am querying the 2nd table the OR ceases to function.  Even
> though I have written as and OR statement it performs the query as if I
> had put:
> 
> SELECT Title, Comments, Genre FROM Music_Library, Genre WHERE (Title
> LIKE 'the' no_case OR Comments LIKE 'the' no_case) AND Genre LIKE 'the'
> no_case

Yes, again, in Valentina 1.x this not works.

I think you can get the same and in the more effective way using FULL OUTER
JOIN.

SELECT Title, Comments, Genre
FROM Music_Library, Genre
WHERE Music_Library.Genre_Ptr *=* Genre.RecID
    Title LIKE 'the' no_case OR Comments LIKE 'the' no_case
    AND     
    Genre LIKE 'the' no_case

Try this


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