Checking the value in a table cell based on a selection from the table.?

Question

SQL

There are tables of users and questions:
**Questions:**
Id
Question
**Variants:**
Id
QuestionId
Variant
**ResultsTable: **
Id
QuestionId
UserId
VariantId
IsTrue
to which answer option belongs, and not any of the questions?
Example:
Question 1- How much is 2+2?
Variant id 1 =8; id 2=4
ResultsTable - User 1 answers question 1 with the answer id 2 (correct)
Question 2- Who is faster?
Variant id 331 ="snail"; id 332="Porsche without wheels"
Without checking question 1, you can answer with a variant from question 2, which is not true ...

Answer 1

I would remove the QuestionId field from the ResultsTable. And IsTrue would be transferred to Variants. After that, it is necessary to control that no one contributes 2 answers to one question to the results. This can be done, for example, with a trigger:

create trigger result_check on ResultsTable after insert, update as
begin
  if exists(
    select 0
    from inserted i
    join ResultsTable r on r.userid=i.userid
    join Variants v on v.id = r.variantid
    group by i.userid
    having count(distinct v.questionid)>1) 
begin
     RAISERROR ('Ошибка - два ответа на один вопрос', 16, 1)
  end
end