Help with this WHERE clause please.....

[AuctionLamb]

Hi guys!

I get my Q.FK_sCust right but why do I still get Cancelled and Closed items returned?


SELECT J.iJob_Status, Q.FK_sCust
FROM JOBS J
LEFT JOIN QUOTES Q
ON J.FK_iQuote_id = Q.PK_iQuote_id
WHERE J.iJob_Status <> '1000 - Cancelled'
AND J.iJob_Status <> '999 - Closed'
AND (Q.FK_sCust = 'ZDAM')
OR (Q.FK_sCust = 'INTEC')
OR (Q.FK_sCust = 'DAMELIN DBN')
OR (Q.FK_sCust = 'INTEC DBN')

 

[gmmastros]

I suspect this is a parenthesis problem. Generally, when you have OR's in where where clause, you need to be a bit more careful withe the parenthesis. Try the following, noting the placement of the parenthesis.

SELECT J.iJob_Status, Q.FK_sCust
FROM JOBS J
LEFT JOIN QUOTES Q
    ON J.FK_iQuote_id = Q.PK_iQuote_id
WHERE J.iJob_Status <> '1000 - Cancelled'
AND J.iJob_Status <> '999 - Closed'
AND (
    (Q.FK_sCust = 'ZDAM')
    OR (Q.FK_sCust = 'INTEC')
    OR (Q.FK_sCust = 'DAMELIN DBN')
    OR (Q.FK_sCust = 'INTEC DBN')
    )

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[vongrunt]

If that's true, you can also make it shorter... and left join becomes inner join because WHERE condition is spreaded over outer table (Q):

SELECT J.iJob_Status, Q.FK_sCust
FROM JOBS J
INNER JOIN QUOTES Q
    ON J.FK_iQuote_id = Q.PK_iQuote_id
WHERE J.iJob_Status NOT IN ('1000 - Cancelled', '999 - Closed')
	AND Q.FK_sCust IN ('ZDAM', 'INTEC', 'DAMELIN DBN', 'INTEC DBN')

------
Math problems? Call 0800-[(10x)(13i)^2]-[sin(xy)/2.362x]