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

[Deane Venske]

Hi Guys,

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

-- 
Deane Venske - Senior Programmer
www.eduss.com.au   deane.venske at eduss.com
+61 7 55974600  +61 405148718
Suite 29,  207 Currumburra Rd
Ashmore, Queensland, 4214
Australia