Linked tables and WHERE clauses. Hoping for a good answer.

[Deane Venske]

On Mon, 09 Feb 2004 10:58:35 +0200, Ruslan Zasukhin 
<sunshine at public.kherson.ua> wrote:

> on 2/9/04 3:58 AM, Deane Venske at deane.venske at eduss.com wrote:
>
> Hi Deane,
>
>> I'm absolutely baffled by what is happening and I really need answers as
>> to why it is happening. I don't want to have to test every query I make 
>> to
>> make sure it works in all cases because of some quirk. It's fine if my
>> query is wrong, but I can't see what could possibly be wrong in the
>> following.
>>
>> Here are 2 queries with their results.
>> SELECT work.*, class.name FROM work, class WHERE class.classID =
>> work.classID AND class.teacherID = 3 AND work.programID = 2 AND
>> work.workType = 2
>> -> Returns all records in the work table even if programID is 1.
>>
>> SELECT work.*, class.name FROM work, class WHERE class.classID =
>> work.classID AND class.teacherID = 3 AND work.workType = 2 AND
>> work.programID = 2
>> -> Returns all records in the work table even if workType is 1.
>>
>> Here are the dumps of the tables.
>> CREATE TABLE class (
>> classID UShort NOT NULL UNIQUE INDEXED,
>> teacherID UShort NOT NULL INDEXED,
>> name VarChar(30,'ASCII') NOT NULL INDEXED
>> );
>>
>> CREATE TABLE work (
>> programID UShort INDEXED,
>> workID UShort NOT NULL UNIQUE INDEXED,
>> workTitle String(50,'ASCII') NOT NULL INDEXED,
>> classID UShort NOT NULL INDEXED,
>> dateSet ULong Long NOT NULL,
>> dateDue ULong Long NOT NULL,
>> workType UShort NOT NULL INDEXED,
>> noQuestions UShort NOT NULL
>> );
>>
>> I can't work out why switching two parts of a where clause around would
>> cause a problem like this.
>
> 1) Valentina 1.x have one glitch.
>     fields on each table must be grouped together in the WHERE clause.
>
> 2) but it seems both your query differ only on order of
>
>             work.programID = 2 AND work.workType = 2
>             work.workType = 2 AND work.programID = 2
>
> So Hmm..
>
> Try to add ()
>
> SELECT work.*, class.name
> FROM work, class
> WHERE class.classID = work.classID
>     AND
>        class.teacherID = 3
>     AND
>        (work.programID = 2 AND work.workType = 2)
>
> Now 100% order must not mater.
>
> If not helps, then I think you need to REINDEX your database.
>
>
>> I did another test adding "AND noQuestions = 3"
>> to the end of this and it returned absolutely everything that had 3 set
>> for that. Now it's looking like it is only looking at the last WHERE
>> clause. Please confirm this, am I doing something wrong, I tested this 
>> in
>> mysql using LEFT JOIN to join the tables and I got the correct results.
>>
>> I am now very worried about other queries throughout my program that I
>> will now need to double check. This is a HUGE concern to me because it
>> really makes it impossible to release the software.
>
Thanks Ruslan,
I'll give it a go in the morning and let you know how it went. I'm holding 
thumbs!

Deane


-- 
Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/