CODEWITHSUNDEEP
sqlsql-serverselect
Shruti
Shruti

Reputation: 281

Exclude records verifying two conditions

select * from table_name
 where BaseDocEntry=15 and BaseDocType='PRRH' and ItemCode='TestStudy2'
   and WhseCode='01' and (currentdoctype<>'PUSH' and CurrentDocEntry<>15)

According to the query I have written above, the data from INS2 will be fetched excluding the currentdoctype [PUSH] and all data where CurrentDocEntry is not 15. What I expect my query to be is, it must exclude the data only if this combination i.e. currentdoctype=PUSH and CurrentDocEntry=15 are occurring in the same row, then exclude that row.

Can you fix this query ?

Upvotes: 10

Views: 21998

Answers (3)

B Misra
B Misra

Reputation: 71

In some cases where NOT does not work you can solve it using a simple case and where clause. This will exclude only those cases where currentdoctype ='PUSH' AND CurrentDocEntry = 15.

SELECT *,
CASE
WHEN currentdoctype ='PUSH' AND CurrentDocEntry = 15 THEN 'c1'
ELSE 'c2'
END AS com_con
FROM table_name
WHERE  BaseDocEntry=15 AND BaseDocType='PRRH' AND ItemCode='TestStudy2'
   AND WhseCode='01' AND com_con = 'c2';

Upvotes: 0

Denys S&#233;guret
Denys S&#233;guret

Reputation: 382474

Use this:

and not (currentdoctype='PUSH' and CurrentDocEntry=15)

Upvotes: 32

aF.
aF.

Reputation: 66757

Since you're using AND and not OR the query is excluding when currentdoctype is different from 'PUSH' and CurrentDocEntry different from 15.

If you want currentdoctype=PUSH and CurrentDocEntry=15 just put those conditions instead:

select * from ins2
where BaseDocEntry=15 and BaseDocType='PRRH' and ItemCode='TestStudy2'
and WhseCode='01' and (currentdoctype='PUSH' and CurrentDocEntry=15)

Upvotes: 1

Related Questions