List:General Discussion« Previous MessageNext Message »
From:Bob Kline Date:November 2 1999 3:52pm
Subject:Re: query- Exists? In? Anything similar
View as plain text  
On Tue, 2 Nov 1999 sinisa@stripped wrote:

> Yuval Malchi writes:
>  > 
>  > 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;
>  > 
>  > But it doesn't seem to work.  anyone has any
>  > suggestions?
>  > 
>  > Thanks,
>  > 
>  > Yuval
>  > 
>  > =====
>  > 
> 
> Hi!
> 
> Would this solve yuour problem:
> 
> select a.CORRECT_ANSWER from a,b where a.CORRECT_ANSWER !=b.ANSWER1
> AND  a.CORRECT_ANSWER !=  b.ANSWER2 AND  a.CORRECT_ANSWER != b.ANSWER3 
> AND  a.CORRECT_ANSWER !=  b.ANSWER4 order by a.CORRECT_ANSWER;
> 
> 

That will give him a join product which is undesirable.  Here's a
modification of that approach which will work:

  SELECT quest_id,
         correct_answer
   WHERE correct_answer NOT IN (answer1, answer2, answer3, answer4)
ORDER BY correct_answer;

Again, best to normalize the tables, but this is probably the best
approach with the existing schema.

-- 
Bob Kline
mailto:bkline@stripped
http://www.rksystems.com

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