CODEWITHSUNDEEP
sql
jfishbow
jfishbow

Reputation: 253

why is query getting records which are not part of where clause?

I have a query 

  select account, collateral,rest_flag_11 ,member_rest_flag11,member_rest_flag12
   from FileDaily
  left join member
  on member.member_account=FileDaily.account
  where member.member_rest_flag11= 80 or member.member_rest_flag12=80
  and FileDaily.REST_FLAG_11=0 and FileDaily.COLLATERAL in (10,11,13,20)

It's getting records where collateral=30,40 which are not part of the collateral in (10,11,13,20), why would this be?

I'm trying to ge tthe account number where in filedaily table collateral=(10,,11,13,20) and rest_flag_11=0 and in the member the member_account is member_rest_flag_11=80 and member_rest_flag12=80.

I use sql server 2012.

Upvotes: 0

Views: 24

Answers (2)

Nebojsa Susic
Nebojsa Susic

Reputation: 1260

You miss parenthesis

select account, collateral,rest_flag_11 ,member_rest_flag11,member_rest_flag12
  from FileDaily
left join member
on member.member_account=FileDaily.account
where (member.member_rest_flag11= 80 or member.member_rest_flag12=80)
and FileDaily.REST_FLAG_11=0 and FileDaily.COLLATERAL in (10,11,13,20)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270443

The problem is presumably parentheses, which you can fix by doing this:

where (member.member_rest_flag11= 80 or member.member_rest_flag12=80) and
     FileDaily.REST_FLAG_11=0 and FileDaily.COLLATERAL in (10,11,13,20)

You can rewrite this using in:

where 80 in (member.member_rest_flag11, member.member_rest_flag12) and
      FileDaily.REST_FLAG_11 = 0 and
      FileDaily.COLLATERAL in (10, 11, 13, 20)

I would also recommend that you use table abbreviations for table aliases. They make queries easier to write and to read.

Upvotes: 2

Related Questions