Newbie Question : Related tables and OR condition

[Deane Venske]

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