SQLTeam.com | Weblogs | Forums

Where condition in Sql

Hello,
I am trying to use following conditions in my where clause but trying make sure that i ma using correct way.
Requirement: need to change, see below:
a. Program <> 35 and acct1 <> 1
b. Program = 30, 31 and acct1 =6
My Where clause is correct as below?
WHERE TYPE <> 35 AND ACCT1 <> 2
AND TYPE IN (30, 31) AND ACCT1 = 6

hi there is a logical issue here
:slight_smile: :slight_smile:

acct1 = 6 means acct1 <> 1

so you can use acct1 = 6 only
no need for acct1 <> 1

unless acct1 in acct1 = 6 is different from acct1 in acct1 <> 1

Thanks Harish.
Not sure by mean that but i think user wants the condition which i have mentioned so i am little confused if i am using both then i am not getting expected results.

Maybe you need an Or in between (just making a guess here), so either rule a. is true or rule b. is true, but even then it is not correct. Run the following sample data, the first 2 rows in the output are fine but the 3rd row contradicts rule b.(assuming you really need an Or condition); until rule a. says Type=35 instead of Type <> 35. I think you may need to revisit your requirement, then tweak this code to check if the result is what you exactly want. Also, un-comment the And clause and see the output as to why your requirement will not work with an And:

DECLARE @TBL TABLE(TYPE INT,ACCT1 INT)

INSERT INTO @TBL (TYPE, ACCT1)
VALUES(1,10),(30,6),(31,15),(35,7),(37,2),(35,2)

SELECT *
FROM @TBL
WHERE (TYPE <> 35 AND ACCT1 <> 2)
--AND (TYPE IN (30, 31) AND ACCT1 = 6)
OR (TYPE IN (30, 31) AND ACCT1 = 6)

TYPE <> 35 AND ACCT1 <> 2 (should this be 1?) already includes TYPES = 30 and 31 and ACCT1 = 6. These are conflicting requirements...

Yes these are conflicting requirements, hopefully the OP runs this example to understand this conflict and rectifies the requirement.

I am agreed that requirements is conflicting.

Thanks everyone for your response.