WHERE clause still returning non selected data

Bone_12 361 Reputation points


I have the following WHERE clause which isn't working and I'm not too sure why.

I have explicitly stated to return select data, but if I use the 'e.cust_field' as an example, it's returning values not specified and I don't know why example '2'.

where a.comp = '0'
and a.mort_code like '%1/%'
and c.start_date >= '2020-01-01' and c.start_date <= '2020-12-31'
and substring(a.cust_no,3,7) <> 'TMU787'
and e.cust_table = '12'
and e.cust_field in ('1','3','7','17','21','29','32','35')
and e.cust_text <> '1258 testing'

Any idea why please?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
9,720 questions
0 comments No comments
{count} votes

Accepted answer
  1. EchoLiu-MSFT 14,516 Reputation points

    Hi @Bone_12

    The unselected data is returned because those data actually satisfy the conditions behind WHERE.

    Maybe this is what you want:

    and (e.cust_field='1' or e.cust_field='3' or e.cust_field='7'or  
    e.cust_field='17' or e.cust_field='21'or e.cust_field='29'or  
    e.cust_field='32' or e.cust_field='35')  

    If you have any question, please feel free to let me know.


    If the answer is helpful, please click "Accept Answer" and upvote it.

    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Olaf Helper 28,701 Reputation points

    and substring(a.cust_no,3,7) <> 'TMU787'

    Your SUBSTRING returns 7 characters and you compare it with 6 characters on unequal?

    Please post table design as DDL, some sample data as DML statement and the expected result.