Where clause problem

[jdavison]

I am running a stored procedure on sql server 7. It runs fine so far except its ignoring one of the where clauses, a.mstatus='OP'. any idea why from the code below?

WHERE
	b.clstatus='C' 
	AND a.mstatus='OP'
	AND b.corgaty='0575' OR a.msupaty='0575'

[barrk]

code:--------------------------------------------------------------------------------
WHERE
b.clstatus='C'
AND a.mstatus='OP'
AND b.corgaty='0575' OR a.msupaty='0575'
--------------------------------------------------------------------------------

Try this

WHERE

b.clstatus='C'
AND a.mstatus='OP'
AND (b.corgaty='0575' OR a.msupaty='0575')

Also make sure that corgaty and msupaty are both character fields...if they aren't you need to remove the tick marks around the value 0575 in both cases.

[jdavison]

Tried that earlier and it still ignored it plus it doesnt give me the right results. For some reason it just wants to completely ignor it. Here is the whole thing.

SELECT
	clname1,
	clnum,
	(SELECT tklast FROM timekeep WHERE tkinit=corgaty),
	(SELECT tkfirst FROM timekeep WHERE tkinit=corgaty),
	mdesc1,
	mmatter,
	(SELECT tklast FROM timekeep WHERE tkinit=msupaty),
	(SELECT tkfirst FROM timekeep WHERE tkinit=msupaty),
	mstatus,
	clstatus
FROM
	matter AS a
	INNER JOIN client AS b
		ON b.clnum=a.mclient
WHERE
	b.clstatus='C' 
	AND a.mstatus='OP'
	AND b.corgaty='0575' 
	OR a.msupaty='0575'

[barrk]

Did you try putting parenthesis around the clause?


WHERE
((b.clstatus='C'
AND a.mstatus='OP')
*must meet both criteria above
AND
(b.corgaty='0575'
OR a.msupaty='0575'))
*must meet one of the two criteria above


((b.clstatus='C' AND a.mstatus='OP') AND (b.corgaty='0575' OR a.msupaty='0575'))

[jdavison]

That worked, thanks

[barrk]

You're welcome