c8450322   
   From: hvandenberg@xs4all.nl   
      
   On 05-04-2011 10:54, Alfaking wrote:   
      
   > SELECT     contr FROM       table1   
   > WHERE     (contr NOT IN   
   > (SELECT     contr   
   > FROM          table1   
   > WHERE      (type = 'T')   
   > GROUP BY contr)   
   >   
   > In my idea this should be ok, but when I double-check the results,   
   > something goes wrong...   
   > no idea why   
      
   It's one of those cases where there's a subtle difference between   
      
   "give me everything where condition  applies"   
      
   yields a different result from   
      
   "leave out everything where condition  does not apply"   
      
   In other words, all squares are geometric shapes, but not all geometric   
   shapes are squares.   
      
      
   If you   
      
   SELECT     contr FROM       table1   
     WHERE     (contr  IN   
     (SELECT     contr   
     FROM          table1   
     WHERE      (type <> 'T')   
     GROUP BY contr)   
      
      
   you will get the expected results.   
      
   Note the difference from your version:   
   WHERE (contr NOT IN ... type = 'T' )   
      
   as opposed to   
   WHERE ( contr IN ... type <> 'T' )   
      
      
   Still, the simple version is preferable   
      
   SELECT DISTINCT contr FROM table1 WHERE type <> 'T'   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)