I also have a Options table that has three columns OptionID, QuestionID and Value
Let's say I want to check how many "ticks" each option for a specific question has got then I can write the following:
SELECT Count(*) Ticks FROM Table1 t INNER JOIN Options o ON t.OptionID = o.OptionID WHERE o.QuestionID = @QuestionID GROUP BY o.OptionID
Now what if I wanted to make a filter where I only want to count "ticks" for each answer where OptionID 1 AND OptionID 4 has been chosen in the answer. I could write:
SELECT Count(*) Ticks FROM (SELECT * FROM Table1 WHERE AnswerID IN (SELECT t1.AnswerID FROM Table1 t1 INNER JOIN Table1 t2 ON t1.AnswerID = t2.AnswerID WHERE t1.OptionID = 1 AND t2.OptionID = 4)) t INNER JOIN Options o ON t.OptionID = o.OptionID WHERE o.QuestionID = @QuestionID GROUP BY o.OptionID
Since the users will be able to create their own filters (he or she might want to filter on alot more options) the above T-SQL has to be dynamic. My question is if there is a better way to accomplish what I am trying to accomplish so that you can get better performance and that you don't have to make the T-SQL dynamic. With more advanced filters there could be lots and lots of JOINS otherwise.