Thread: Back Up Alt Products

I am currently stuck with my SQL.
We want to pull the top 3 products in a category on the product page. We will use this if the alternative products are empty. Thus we will always have alternative products for the customer regardless if we pre set them or not.

My problem is the code runs and pulls the top products from all categories and just shows the category id as the product been displayed.
note. I am only pulling pc.CategoryID and p.Looks for testing to see what is being returned. They will be removed once I have it working.

C#/VB.NET Code:

SELECT TOP 3
    pc.CategoryID,
    p.Looks,
    p.ProductID,
    p.SEName,
    p.Summary,
    p.SKU,
    pv.SKUSuffix,
    p.Name AS ProductName,
    ISNULL(pv.SalePrice, pv.Price) AS ProductPrice
FROM
    dbo.Product p WITH (nolock)
JOIN
    dbo.ProductVariant pv WITH (nolock) ON pv.ProductID = p.ProductID
JOIN
    dbo.ProductCategory pc WITH (nolock) ON pc.ProductID = 94
WHERE
    p.Deleted = 0  
    and p.Published = 1
    and pv.IsDefault = 1
    and p.IsCallToOrder = 0 
    and p.productid != 94
ORDER BY 
    p.Looks desc 


Can anyone shed some light on why I can not pull products in a set category correctly.
Also the number '94' is just a product ID we have, in the XML package the '94' would be replaced by the currently viewed product ID.

Many thanks in advance.

Figured it out

C#/VB.NET Code:

SELECT TOP 3
    pc.CategoryID,
    p.Looks,
    p.ProductID,
    p.SEName,
    p.Summary,
    p.SKU,
    pv.SKUSuffix,
    p.Name AS ProductName,
    ISNULL(pv.SalePrice, pv.Price) AS ProductPrice
FROM
    dbo.Product AS p WITH (nolock)
JOIN
    dbo.ProductVariant AS pv WITH (nolock) ON pv.ProductID = p.ProductID
JOIN
    dbo.ProductCategory AS pc WITH (nolock) ON pc.ProductID = pv.ProductID
WHERE
    p.Deleted = 0
    and p.Published = 1
    and pv.IsDefault = 1
    and p.IsCallToOrder = 0
    and p.productid != 650
    and pc.CategoryID = 23
ORDER BY
    p.Looks desc 


I just need to put in the CategoryID of the current category the product belongs to and the current product so it does not show up as its own alternative!

If anyone knows a better way please do post it!

Toad for SQL Optimizer reports that this might be quicker, probably because it uses the indexes on ProductCategory and ProductVariant

Code:

SELECT TOP 3 pc.CategoryID, 
       p.Looks, 
       p.ProductID, 
       p.SEName, 
       p.Summary, 
       p.SKU, 
       pv.SKUSuffix, 
       p.Name AS ProductName, 
       ISNULL(pv.SalePrice, pv.Price) AS ProductPrice 
  FROM dbo.Product AS p WITH (nolock) 
       INNER JOIN dbo.ProductVariant AS pv WITH (nolock) 
          ON p.ProductID = pv.ProductID 
       INNER JOIN dbo.ProductCategory AS pc WITH (nolock) 
          ON p.ProductID = pc.ProductID 
 WHERE p.Deleted = 0 
   and p.Published = 1 
   and pv.IsDefault = 1 
   and p.IsCallToOrder = 0 
   and p.productid <> 650
   AND pv.ProductID <> 650
   AND pc.ProductID <> 650
   and pc.CategoryID = 23
 ORDER BY p.Looks desc

Thanks for that update. I have included the extra lines in my code. When I figure out how to call the categoryid into an SQL statement in my XML package I will post the code for you

Got it working using the lastviewedentitiyinstancedid as suggested by BFG in this thread

C#/VB.NET Code:

  <!-- CAT ALTERNATIVE PRODUCTS -->
  <query name="CatProductAlternatives" rowElementName="CatAlternative">
    <sql>
      <![CDATA[
      SELECT TOP 3
          p.ProductID,
          p.SEName,
          p.Summary,
          p.SKU,
          pv.SKUSuffix,
          p.Name AS Name,
          ISNULL(pv.SalePrice, pv.Price) AS ProductPrice
      FROM
          dbo.Product AS p WITH (nolock)
      JOIN
          dbo.ProductVariant AS pv WITH (nolock) ON pv.ProductID = p.ProductID
      JOIN
          dbo.ProductCategory AS pc WITH (nolock) ON pc.ProductID = pv.ProductID
      WHERE
          p.Deleted = 0
          and p.Published = 1
          and pv.IsDefault = 1
          and p.IsCallToOrder = 0
          and p.ProductID != @productid
        and pv.ProductID != @productid
        and pc.ProductID != @productid
        and pc.CategoryID = @categoryid
      ORDER BY
          p.Looks desc
            ]]>
    </sql>
    <queryparam paramname="@categoryid" paramtype="request" requestparamname="lastviewedentityinstanceid" sqlDataType="int" defvalue="0" validationpattern="" />
    <queryparam paramname="@ProductID"       paramtype="request"    requestparamname="ProductID"                                  sqlDataType="int" defvalue="0"  validationpattern="" />
  </query>
  <!-- END CAT ALTERNATIVE PRODUCTS --> 


Thanks guys!
For the future to improve this I will have to work out how to get it working by product ID as this does have its holes (i.e. direct access of the page or via the manufacturer page etc.)