home bbs files messages ]

Forums before death by AOL, social media and spammers... "We can't have nice things"

   comp.databases.ms-sqlserver      Notorious Rube Goldberg contraption      19,505 messages   

[   << oldest   |   < older   |   list   |   newer >   |   newest >>   ]

   Message 18,193 of 19,505   
   Lutz Uhlmann to All   
   Re: I Need help with a query...   
   05 Apr 11 11:50:40   
   
   c8450322   
   From: news@invalid.invalid   
      
   Am 05.04.2011 10:54, schrieb Alfaking:   
   > On 5 Apr, 10:09, Henk van den Berg  wrote:   
   >   
   >> Please post the query that you have used.   
   >   
   > In my idea this should be ok, but when I double-check the results,   
   > something goes wrong...   
   > no idea why   
      
   When I run   
      
   SELECT *   
   FROM dbo.table1   
   WHERE contr NOT IN (SELECT contr FROM dbo.table1 WHERE (type = 'T')   
     GROUP BY contr)   
      
   i get this:   
   ID	Contr	Type   
   3	5555	W   
   5	7777	W   
   9	7777	W   
      
   I think this is the correct result!   
      
   Anyway there is also an alternative solution   
      
   SELECT t1.*   
   FROM dbo.table1 as t1 LEFT JOIN (SELECT contr FROM dbo.table1 WHERE   
     (type = 'T') GROUP BY contr) AS t2 ON t1.contr=t2.contr   
   WHERE t2.contr IS NULL   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)   

[   << oldest   |   < older   |   list   |   newer >   |   newest >>   ]


(c) 1994,  bbs@darkrealms.ca