SQLTeam.com | Weblogs | Forums

Issues with WHERE IN


#1

I'm having an issue with my WHERE statement. Here's the trouble script:

SELECT DISTINCT s.student_ssn,
                award_amount,
                year_in_school

FROM [Test2005].[dbo].[previous_awards] pa
JOIN student s ON pa.student_token=s.student_token
JOIN user_string us ON s.student_token=us.student_token
JOIN funds f ON pa.fund_token=f.fund_token
WHERE value_122 = 'Y'
  AND us.award_year_token='2013'
  OR us.award_year_token='2014'
  AND (f.fund_short_name='Perkins'
       OR f.fund_short_name='Staff Sub'
       OR f.fund_short_name='Staff Unsb'
       OR f.fund_short_name='Direct Sub'
       OR f.fund_short_name='Direct Uns')
  AND award_amount>'0'
  AND (year_in_school='1'
       OR year_in_school='2'
       OR year_in_school='3'
       OR year_in_school='4'
       OR year_in_school='5') 

ORDER BY student_ssn

However, I'm getting results where the year_in_school is 6, or 7, or 8. Why is that? What am I overlooking?

Example results:

| student_ssn | award_amount | year_in_school |
|-------------|--------------|----------------|
| 100101000   | 22125        | 8              |
| 987654321   | 875          | 1              |
| 123456789   | 875          | 3              |
| 589760000   | -875         | 7              |
| 444113333   | 5726         | 5              |
| 101894444   | 4785         | 6              |

Thanks in advance!


#2

Because of the "OR" against us.award_year_token without parentheses -- you need to add parentheses around it:

WHERE value_122 = 'Y'
  AND (us.award_year_token='2013' --<< added (
  OR us.award_year_token='2014') --<< added )
...

#3

Alternative you might want to use the "in" operator - I think it makes it look much cleaner.

WHERE value_122 = 'Y'
  AND us.award_year_token in ('2013','2014')
  AND f.fund_short_name in ('Perkins','Staff Sub','Staff Unsb','Direct Sub','Direct Uns')
  AND award_amount>'0'
  AND year_in_school in ('1','2','3','4','5')