IN and NOT IN


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 1 of 1

Thread: IN and NOT IN

  1. #1
    Join Date
    Mar 2009
    Posts
    1

    IN and NOT IN

    Hi,

    I'm having a bit of trouble with a SELECT query.

    I'm trying to select a count of the number of products which are not part of Invoices, Sales Orders etc.

    The code I have so far is....
    Code:
    SELECT COUNT(*) FROM tbProdDesc WHERE
        [Stock Code] NOT IN 
        (SELECT [Stock Code] FROM tbQuotePart UNION 
         SELECT [Stock Code] FROM tbInvoicePart UNION
         SELECT [Stock Code] FROM tbSOPart UNION
         SELECT [Stock Code] FROM tbBIPart UNION
         SELECT [Stock Code] FROM tbPOPart)
    However this brings back 0, when it should be over 28000.

    If i change it to In, instead of NOT IN i.e.
    Code:
    SELECT COUNT(*) FROM tbProdDesc WHERE
        [Stock Code] IN 
        (SELECT [Stock Code] FROM tbQuotePart UNION 
         SELECT [Stock Code] FROM tbInvoicePart UNION
         SELECT [Stock Code] FROM tbSOPart UNION
         SELECT [Stock Code] FROM tbBIPart UNION
         SELECT [Stock Code] FROM tbPOPart)
    This brings back 700 odd records which is correct.

    For some reason it does not like my NOT IN command.

    Any help is much apprecitaed.

    Thanks
    Last edited by Hack; 03-30-2009 at 12:46 PM. Reason: Added Code Tags

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center