I have 2 table named OptionText
and SubmittedAns
The first table contains the QuestionId
OptionId
and IsRight
. Here IsRight
is used for either the options against the question is either right or wrong.Here a QuestionId
have multiple OptionId
even can have one too. The second table represent what is submitted by the user. They can either select one option or multiple option based on their assumption. Now I need to make automatic script which will justify either the submitted answers against right or wrong.
Note: If a question have more than one correct option then the user have to select all right option, if one is missing then the result will be false. However if he selects all right answers then the answer will be true.
I have tried through this script. It's just able to count the selected numbers but can't justify the answer is either right or wrong. So I need help.
I can assume that I need a WHILE
loop to check each element particularly. But how? So I need help. Here is my code.
CREATE TABLE OptionText(
[OptionTextId] [bigint] IDENTITY(1,1) NOT NULL,
[QuestionId] [bigint] NOT NULL,
[IsRightAnswer] [bit] NOT NULL)
Insert into OptionText (QuestionId, IsRightAnswer) VALUES (5, 1)
Insert into OptionText (QuestionId, IsRightAnswer) VALUES (5, 0)
Insert into OptionText (QuestionId, IsRightAnswer) VALUES (5, 0)
Insert into OptionText (QuestionId, IsRightAnswer) VALUES (17, 0)
Insert into OptionText (QuestionId, IsRightAnswer) VALUES (17, 1)
Insert into OptionText (QuestionId, IsRightAnswer) VALUES (17, 1)
CREATE TABLE SubmittedAns(
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[QuestionId] [bigint] NOT NULL,
[OptionTextId] [bigint] NOT NULL)
Insert into SubmittedAns (QuestionId, OptionTextId) VALUES (5, 1)
Insert into SubmittedAns (QuestionId, OptionTextId) VALUES (5, 2)
Insert into SubmittedAns (QuestionId, OptionTextId) VALUES (2, 1)
Insert into SubmittedAns (QuestionId, OptionTextId) VALUES (2, 1)
select * from OptionText
select * from SubmittedAns
if (select count(OptionTextId) from SubmittedAns where QuestionId =5) = (select count(ot.OptionTextId) from OptionText as ot where ot.IsRightAnswer = 1)
select 1 as "isRight"
else
select 0 as "isRight"
Please refer to first & last line for critical material:
SELECT CASE COUNT(*) WHEN 0 THEN 'Pass' ELSE 'Fail' END AS Boolean
FROM (
SELECT *
FROM #OptionText
WHERE QuestionId = 5
AND IsRightAnswer = 1
) AS OT
FULL OUTER JOIN #SubmittedAns AS SA ON OT.QuestionId = SA.QuestionId AND OT.OptionTextId = SA.OptionTextId
WHERE SA.QuestionId = 5
AND OT.OptionTextId IS NULL -- This means some answers failed to be matched with your original questions/options, either because IsRightAnswer is zero, or because it doesn't exist in your questions/answers.