Yuval Malchi wrote:
>
> I have this table:
>
> quest_id | ans1 | ans2 | ans3 | ans4 | correct_ans
>
> I'm trying to write a query that finds errors in the
> table, where a correct answer does not exist in one of
> the choices.
>
> select a.CORRECT_ANSWER
> from DAILY_QUESTION a
> where a.CORRECT_ANSWER NOT IN
> (select b.ANSWER1, b.ANSWER2, b.ANSWER3,
> b.ANSWER4
> from DAILY_QUESTION b)
> order by a.CORRECT_ANSWER;
>
How about:
select CORRECT_ANSWER,
if(CORRECT_ANSWER == ANSWER1, 1, 0) as a,
if(CORRECT_ANSWER == ANSWER2, 1, 0) as b,
if(CORRECT_ANSWER == ANSWER3, 1, 0) as c,
if(CORRECT_ANSWER == ANSWER4, 1, 0) as d
from DAILY_QUESTION
where a = 0 and b =0 and c = 0 and d = 0
order by CORRECT_ANSWER;
jim...
