Search code examples
sql-serverdynamicquery

How can I compare multiple rows with other rows from two different table and check if they are equal or not?


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"

Solution

  • 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.