October 6, 2009 at 5:14 pm
I have an interesting question to ask. I have a statement in which users want to be able to select and apply multiple filters to the same data set.
I am counting records by a test.
SELECT COUNT(Score) AS myScore, TestID
FROM dbo.vw_DetailResults
WHERE (CountyID = 12645679)
AND (CityID = 2123449)
AND (TestID = 1) AND (Score >= 5)
GROUP BY TestID
What I need to do is add an additional qualifier to this statement and it be
Or TestID = 2 and Score >=2
If I add that statement to the SQL above, it will apply both conditions. And if I place it in a IN statement, it will not limit it properly.
What is the best way to handle this? Does it need to be forced into multiple selects? If that is so, any way to join those together into 1 data set at the db level?
October 6, 2009 at 5:24 pm
Wherever you've got "grouped filters", ie. filters that need to be applied togather rather than individually, you need to put parentheses around them. Therefore the additional filter you want to apply should read:
...
or (TestID = 2 and Score >= 2)
...
October 6, 2009 at 5:24 pm
Nevermind, if I Union it all is well. One of those things that sits at the back of your mind for hours.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply