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

[Ruslan Zasukhin]

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.

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