Get Product By Id

[Sergey]

Hi all,

Please show how to make an query to get a product by ID which was not sold.
(Sorry, if the current question has already been)

Thanks.

[webopius]

Hi,
Something like this should work which will return a list of all published (current) products that have NEVER sold ordered by SKU.

You could also change the OrdersShoppingCart SQL query to specify date ranges, link to specific Customers etc.

select p.ProductID, p.SKU, p.Name
from Product p
where
p.ProductID not in 
(select distinct 
os.productid from Orders_ShoppingCart os)
and p.Deleted = 0
and p.Published = 1
order by p.sku, p.name

[Sergey]

Thanks a lot webopius, it's works!

[SWillis]

Some minor tweaking of this query produces a better execution plan. Using the EXCEPT clause instead of WHERE NOT IN (or WHERE NOT EXISTS) combined with adding the ProductID Primary Key in the first WHERE clause allows the query to do a Clustered Index Seek on the Product table and an Index Seek on the Order_ShoppingCart table rather than Index scans. The index seek on the Order_ShoppingCart table uses an existing IX so creating anything new or non-standard is not required. The difference will seem negligible on small data sets but will be much more robust as the number of orders increases over time.

SELECT DISTINCT
    p.ProductID
   ,p.SKU
   ,p.Name
FROM
    Product p
WHERE
    p.ProductID > 0
    AND p.Deleted = 0
    AND p.Published = 1
EXCEPT
SELECT
    p.ProductID
   ,p.SKU
   ,p.Name
FROM
    Product p
    INNER JOIN 
        Orders_ShoppingCart os
        ON p.ProductID = os.ProductID
ORDER BY
    p.sku
   ,p.name

Query plan of original query:
Query_Plan_Original_Query.jpg

Query plan of updated query:
Query_Plan_Updated_Query.jpg

*