List:General Discussion« Previous MessageNext Message »
From:Jim Faucette Date:November 2 1999 1:07pm
Subject:Re: query- Exists? In? Anything similar
View as plain text  
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...
Thread
query- Exists? In? Anything similarYuval Malchi2 Nov
  • Re: query- Exists? In? Anything similarJim Faucette2 Nov
  • Re: query- Exists? In? Anything similarBob Kline2 Nov
    • Re: query- Exists? In? Anything similarBob Kline2 Nov
  • Re: query- Exists? In? Anything similarsinisa2 Nov
    • Re: query- Exists? In? Anything similarBob Kline2 Nov