Where Strange Results

Question

I'm using this query to obtain payments from customer still not payed. So I need to exclude all kind in the where clause. I tryed with <> and != and Not Like but It show me all payment, also with those I've excluded in the where clause. Do you see something that I don't see?

SELECT td= CODCLIFOR,      '', 
            td= DSCCONTO1,      '',  
            td= ANAGRAFICACF.TELEX,      '', 
            td= NUMDOC,      '', 
            td= NUMSCAD,      '', 
            td= CONVERT (VARCHAR(30),DATASCADENZA,105),      '', 
            td= '€ ' + REPLACE(CONVERT(varchar, CAST(IMPORTOSCLIT AS money), 105),',','.') ,       '', 
            td= TIPIEFFETTI.DESCRIZIONE,ESITO
            FROM dbo.TABSCADENZE
            INNER JOIN dbo.ANAGRAFICACF ON CODCLIFOR = CODCONTO
            INNER JOIN dbo.TIPIEFFETTI ON TIPOEFFETTO = EFFETTO
            INNER JOIN dbo.BANCAAPPCF ON CODCLIFOR = BANCAAPPCF.CODCONTO AND BANCAAPPCF.CODICE = TABSCADENZE.BANCAAPPOGGIO
            INNER JOIN  dbo.ANAGRAFICABANCHE ON BANCAINC = CODBANCA
            WHERE  (TABSCADENZE.TIPOEFFETTO NOT LIKE '%2%' OR TABSCADENZE.TIPOEFFETTO NOT LIKE '%10%') AND (TABSCADENZE.ESITO NOT LIKE '%1%' OR  TABSCADENZE.ESITO NOT LIKE '%2%' OR  TABSCADENZE.ESITO NOT LIKE '%4%')  AND  CODCLIFOR LIKE '%C%'
            ORDER BY CODCLIFOR DESC

Thank You!

Please [edit] to include sample data and the output you'd like to obtain from that data. The only thing that's clear from your question is that you appear to have a very poor database design. — Ken White, Aug 09 '16 at 22:18

score 0 · Answer 1 · edited May 23 '17 at 12:33

It's the combination of ORs and ANDs, and using the NOT that are confusing and wrong.

Don't read them like English because that's where most people incorrectly think they've coded the right logic.

If you use OR with NOTs, then you need to do the reverse and replace OR with ANDs. Otherwise one will fail, but the other will pass and hence the entire WHERE becomes TRUE which is why all your records are showing.

Try this...

WHERE
    TABSCADENZE.TIPOEFFETTO NOT LIKE '%2%' 
AND TABSCADENZE.TIPOEFFETTO NOT LIKE '%10%'
AND TABSCADENZE.ESITO NOT LIKE '%1%' 
AND TABSCADENZE.ESITO NOT LIKE '%2%' 
AND TABSCADENZE.ESITO NOT LIKE '%4%'
AND CODCLIFOR LIKE '%C%'

Have a look at this which is similar...

How do I add multiple "NOT LIKE '%?%' in the WHERE clause of sqlite3 in python code?

mmm I'm not sure that put only AND it's the right way. Now I have noone result. `ESITO` can be or 1 or 2 or 4 and can't be at the same time the 3 values, so I think that the result, like this is always empty. And the same is for `TIPOEFFETTO` — BigBlack, Aug 10 '16 at 09:14

score 0 · Answer 2 · answered Aug 10 '16 at 14:07

Ok I've found the solution. I Used the Not In with subqueries. Now results are good. this is the code if someone need it:

WHERE DATASCADENZA BETWEEN DATEADD(DAY, -30, GETDATE()) AND DATEADD(DAY, -15, GETDATE()) AND CODCLIFOR LIKE '%C%' 
                AND TIPOEFFETTO NOT IN (SELECT DISTINCT TIPOEFFETTO FROM dbo.TABSCADENZE WHERE TIPOEFFETTO = 10 OR TIPOEFFETTO = 2 ) AND ESITO NOT IN (SELECT DISTINCT ESITO FROM dbo.TABSCADENZE WHERE ESITO = 1 OR ESITO = 2 OR ESITO = 4)

Thank you for your help!

It's usually good community ethos to give a point to answers even if they were not 100% what you've achieved. — Fandango68, Aug 13 '16 at 23:06

Where Strange Results

2 Answers2