0

I have only tried to replace NOT IN operator with NOT EXISTS in the following code. However, it is giving me an "Invalid Relational Operator error".

How can I correct this error? Am I missing something important? Please help.

SELECT DISTINCT 'v1',
                   'v2',
                   f.apples,
                   f.bananas,
                   f.oranges     
FROM fruits f
     WHERE f.code = 'F1' 
     AND f.buyer_fruit_code
     NOT EXISTS
             (SELECT c.color_code
                FROM colors c);
Sparky
  • 14,967
  • 2
  • 31
  • 45
codeforme
  • 25
  • 2
  • 5

2 Answers2

2

The NOT IN operator means something different from a NOT EXISTS clause. Among other things, NOT EXISTS is not an operator. Perhaps you want something like this:

SELECT DISTINCT 'v1',
               'v2',
               f.apples,
               f.bananas,
               f.oranges     
FROM fruits f
WHERE
  f.code = 'F1' 
  AND NOT EXISTS (
    SELECT c.color_code
    FROM colors c
    WHERE c.color_code = f.buyer_fruit_code
  )
;
John Bollinger
  • 160,171
  • 8
  • 81
  • 157
0

Try this instead:

SELECT DISTINCT 'v1',
                       'v2',
                       f.apples,
                       f.bananas,
                       f.oranges     
    FROM fruits f
    LEFT JOIN color_code c ON f.fruit_code=c.color_code
         WHERE f.code = 'F1' 
         AND c.color_code is NULL
Sparky
  • 14,967
  • 2
  • 31
  • 45
  • Thanks I would love to try this but I'm specifically looking to replace 'NOT IN' with 'NOT EXISTS' here. – codeforme Dec 22 '14 at 18:37