IN and NOT IN
IN and NOT IN
(OP)
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
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
RE: IN and NOT IN
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
RE: IN and NOT IN
CODE
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: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?
RE: IN and NOT IN
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.
RE: IN and NOT IN
CODE
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: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?
RE: IN and NOT IN
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.
RE: IN and NOT IN
If one (or more) stock codes are NULL, the NOT IN-SELECT will return 0 rows.