Newbie Question : Related tables and OR condition

[Greg DeVore]

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

I tried using the Genre_Ptr = Genre.RecID AND (Genre LIKE 'the' 
no_case) but I get the same results.


On Thursday, September 18, 2003, at 09:46 PM, Deane Venske wrote:

> Greg DeVore wrote:
>
>>
>>
>> I am having trouble with OR statements between two related tables.
>> My SQL is this:
>>
>> SELECT Title, Genre, Mood, Tempo, Comments, Composer, Collection, 
>> DVD, Publisher FROM Music_Library, Genre, Mood, Tempo, Composer, 
>> Collection, DVD, Publisher WHERE Title LIKE 'the' no_case OR MOOD 
>> LIKE 'tive'
>> The problem is that Valentina is treating this as if I had put
>> "WHERE Title LIKE 'the' no_case AND MOOD LIKE 'tive'"
>>
>> But I want OR
>> If I do this between two fields from the same table the OR condition 
>> works as expected.
>> It is only when using an OR condition between two tables that I am 
>> having problems.
>>
>> Any help would be greatly appreciated.
>> Greg DeVore
>>
>> _______________________________________________
>> Valentina mailing list
>> Valentina at lists.macserve.net
>> http://lists.macserve.net/mailman/listinfo/valentina
>>
> As far as I can see in this SQL there are more than one table there. 
> ONCE you write a FROM you start listing tables, so unless you're 
> querying tables called Music_Library, Genre, Mood, Temp, Composer, 
> Collectoin, DVD and publisher then I think your SQL is wrong. Have you 
> tried just doing a query on the single table using OR? I've tested 
> Valentina with ORs and they work perfectly. If you can paste a dump of 
> your two tables I could help you with the query if you want. Have you 
> created the tables with foreign keys? If not you'll have to specify 
> the relation in the SQL here is a quick example :
> Table 1 - user
> userID
> username
> password
>
> Table 2 - userDetails
> userID
> firstName
> lastName
> userType
>
> So if I want to select the first and last names of all users that are 
> set to userType teacher OR admin I'd do this :
>
> SELECT firstName, lastName FROM userDetails, user WHERE 
> userDetails.userID = user.userID AND (userType = 'teacher' OR userType 
> = 'admin')
>
> The userDetails.userID = user.userID is my relation between the two 
> tables.
>
> Regards,
> Deane
>
>
> _______________________________________________
> Valentina mailing list
> Valentina at lists.macserve.net
> http://lists.macserve.net/mailman/listinfo/valentina
>
Greg DeVore
music at gregdevore.com
661-753-9335