Validation SQL query result

[karl Jonson 0]

Hi,
I am trying to process results to this question:
What optional subjects do you study?
Results could be:
1 Option1
2 Option2
8 None
9 NotKnown

 

I have the results in a SQL query result e.g.

OptionCode
1
2
9

 

I would like to implement these rules on the query result:
If duplicate codes then invalid result
if 8 & another code then invalid result
if 9 & another code then invalid result

 

[Options table]
1 Option1
2 Option2
8 None
9 NotKnown

[Answers table]
Student Answer
100     1
100     2

101     1
101     9

Student 100 answers are valid.
Student 101 answers are not valid because they break rule number3

 

Which is the best way to implement these 3 rules ?

 

I want to do this:

ValidateStudentAnswers(100) => result : valid

ValidateStudentAnswers(101) => result : not valid

 

TIA

Edited November 3, 2022 by karl Jonson

[Stano 139]

In my opinion, this is a typical example of failure - bad logic. Simply, the user cannot/must not enter such a combination. You have to solve that on the client.

[skyzoframe[hun] 4]

I dont know what are you talking about. What is your question? In one sentence..

[skyzoframe[hun] 4]

I got the answer. 42.

[karl Jonson 0]

I would like to do this:

ValidateStudentAnswers(100) => result : valid

ValidateStudentAnswers(101) => result : not valid

[rvk 29]

39 minutes ago, karl Jonson said:

I would like to do this:

ValidateStudentAnswers(100) => result : valid

ValidateStudentAnswers(101) => result : not valid

Something simple like this will at least check (2) and (3). It won't check (1) but that should be done at entry (only allow unique student/answer combinations).

select student, if((count(*)>1) and (max(answer)>7), 'INVALID', 'Valid') as Result
from data1
group by student

https://dbfiddle.uk/d-QSR_4l

 

student	Result
100	Valid
101	INVALID

 

If you really want to check (1) you can do it with a sub-select with count on total number of answers and total number of unique answers.

 

Something like:

select student, if((sum(cnt)<>count(*)) or ((count(*)>1) and (max(answer)>7)), 'INVALID', 'Valid') as Result
from
(  
  select student, answer, count(*) as cnt
  from data1
  group by student, answer
) as sub
group by student

https://dbfiddle.uk/B3JmU1_S 

student	Result
100	Valid
101	INVALID
102	INVALID
103	Valid
104	INVALID