IN and NOT IN

ashooo · Mar 30, 2009

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....

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.

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

IanWaterman · Mar 30, 2009

why not use a join

SELECT COUNT(*) FROM tbProdDesc p
left outer join
(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) u
on p.Stock Code = u.Stock Code
where u.stock Code is null

PHV · Mar 30, 2009

You may also try this:

Code:

SELECT COUNT(*) FROM tbProdDesc WHERE
    NOT ([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))

BTW, does tbProdDesc has rows with NULL [Stock Code] ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

ashooo · Mar 30, 2009

IanWaterman - Your select statement works but I need to turn it into a Delete statement, and I'm not sure of the syntax changes I would need when deleting with a joined query.

PHV - Your select statement still brings back 0, when it should be 28000 ish. There are no rows with Null [Stock Code].

Thanks for your help.

PHV · Mar 30, 2009

And this ?

Code:

SELECT COUNT(*) FROM tbProdDesc 
WHERE [Stock Code] NOT IN (SELECT [Stock Code] FROM tbQuotePart)
  AND [Stock Code] NOT IN (SELECT [Stock Code] FROM tbInvoicePart)
  AND [Stock Code] NOT IN (SELECT [Stock Code] FROM tbSOPart)
  AND [Stock Code] NOT IN (SELECT [Stock Code] FROM tbBIPart)
  AND [Stock Code] NOT IN (SELECT [Stock Code] FROM tbPOPart)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

ashooo · Mar 30, 2009

Still returns 0.

I managed to do it by adopting IanWaterman's code.

DELETE tbProdDesc FROM
(
SELECT p.* FROM tbProdDesc p
left outer join
(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) u
on p.[Stock Code] = u.[Stock Code]
WHERE u.[Stock Code] IS NULL)
) AS t1
WHERE tbProdDesc.[Stock Code] = t1.[Stock Code]

Thanks for all your help, it's much appreciated.

JarlH · Mar 30, 2009

What about NULL's?

If one (or more) stock codes are NULL, the NOT IN-SELECT will return 0 rows.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

IN and NOT IN

ashooo

Programmer

IanWaterman

Programmer

PHV

MIS

ashooo

Programmer

PHV

MIS

ashooo

Programmer

JarlH

Programmer

Similar threads

Part and Inventory Search

Sponsor