Important Notice from AspDotNetStorefront
It is with dismay that we report that we have been forced, through the action of hackers, to shut off write-access to this forum. We are keen to leave the wealth of material available to you for research. We have opened a new forum from which our community of users can seek help, support and advice from us and from each other. To post a new question to our community, please visit: http://forums.vortx.com
Results 1 to 4 of 4

Thread: Get Product By Id

  1. #1
    Sergey is offline Junior Member
    Join Date
    Mar 2012
    Posts
    15

    Default Get Product By Id

    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.

  2. #2
    webopius is offline Senior Member
    Join Date
    Nov 2008
    Location
    London, UK
    Posts
    440

    Default

    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.

    Code:
    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

  3. #3
    Sergey is offline Junior Member
    Join Date
    Mar 2012
    Posts
    15

    Default

    Thanks a lot webopius, it's works!

  4. #4
    SWillis is offline Junior Member
    Join Date
    May 2012
    Location
    Charlotte, NC
    Posts
    9

    Default Get Product By ID

    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.

    Code:
    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:
    Name:  Query_Plan_Original_Query.jpg
Views: 30
Size:  56.2 KB

    Query plan of updated query:
    Name:  Query_Plan_Updated_Query.jpg
Views: 30
Size:  69.5 KB

    *