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
Talk To Other Members
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.