A client recently asked me "How do I create an SQL statement that lists all the products that have not generated any sales, that can be used with either Crystal Reports or SQL Server Reporting Services?"
Using SQL Server 2000:
SELECT Product .[Product ID]
FROM Product
WHERE
NOT EXISTS
(
SELECT [Orders Detail].[Product ID]
FROM [Orders Detail]
WHERE Product.[Product ID]=[Orders Detail].[Product ID])
Using SQL Server 2005, the above SQL statment can be simplified using the EXCEPT command:
SELECT Product .[Product ID],
Product .[Product Name]
FROM Product
EXCEPT
SELECT [Orders Detail].[Product ID]
FROM [Orders Detail]
If you have any questions, or need any
assistance, please do not hesitate to Contact Us
|
