|
|
----------->SELECT tInventoryWebData.InventoryID, tInventoryWebData.WebFeaturedItem, tInventoryWebData.DisplayOnWebSite, tInventoryWebCategories.CategoryID As WebCategoryID, tInventoryWebData.IncRRP, tInventory.Warranty, tInventoryWebData.CompanyID, tInventory.Condition, tInventory.ItemTitle, tInventory.DescriptionHTML, tInventory.DescriptionText, tInventory.Weight, tInventory.QtyInStock, tInventory.MinimumOrderQty, tInventory.BarcodeID, tInventory.AlwaysInStock, ISNULL(C.RRP, 0) AS RRP, tInventory.OutOfStockQTYRetail, ISNULL(A.QtyFrom, 0) AS MinQTY, ISNULL(B.QtyTo, 0) AS MaxQTY, ISNULL(AA.Price,0) AS MinPrice, tInventoryWebData.PostageCostMethodDom, tInventoryWebData.PostageCostMethodInt, tInventoryWebData.ApplyFPRange, tInventoryWebData.ApplyWSFPRange, tInventoryWebData.WebTitle, tInventoryWebData.DisableCheckout, ISNULL(AA.VariationKeyID, '{00000000-0000-0000-0000-000000000000}') AS VariationID, tInventoryWebData.URLKey FROM tInventoryWebData INNER JOIN tInventory ON tInventoryWebData.InventoryID = tInventory.InventoryID AND tInventoryWebData.CompanyID = '00000000-0000-0000-0000-000000000005' INNER JOIN tInventoryWebCategories on tInventoryWebData.InventoryID = tInventoryWebCategories.InventoryID AND tInventoryWebCategories.IsDefaultWebCategory = 1 AND tInventoryWebCategories.CompanyID = '00000000-0000-0000-0000-000000000005' LEFT JOIN tInventoryWebPrices A ON A.PriceID = ( SELECT Top 1 tInventoryWebPrices.PriceID FROM tInventoryWebPrices JOIN tInventoryWebPriceVariations on tInventoryWebPrices.PriceID = tInventoryWebPriceVariations.PriceID WHERE tInventoryWebPrices.InventoryID = tInventoryWebData.InventoryID AND tInventoryWebPrices.CompanyID = tInventoryWebData.CompanyID AND tInventoryWebPrices.IsWholesale = 0 ORDER BY tInventoryWebPrices.QtyFrom ASC, tInventoryWebPriceVariations.Price ASC ) LEFT JOIN tInventoryWebPrices B ON B.PriceID = ( SELECT Top 1 PriceID FROM tInventoryWebPrices WHERE tInventoryWebPrices.InventoryID = tInventoryWebData.InventoryID AND tInventoryWebPrices.CompanyID = tInventoryWebData.CompanyID AND tInventoryWebPrices.IsWholesale = 0 ORDER BY QtyTo DESC ) LEFT JOIN tInventoryWebPriceVariations AA ON A.InventoryID = AA.InventoryID AND A.PriceID = AA.PriceID AND AA.VariationKeyID = (Select Top 1 VariationKeyID from tInventoryWebPriceVariations where PriceID = A.PriceID ORDER BY price ASC) LEFT JOIN tInventoryWebPriceVariations BB ON B.InventoryID = BB.InventoryID AND B.PriceID = BB.PriceID AND BB.VariationKeyID = (Select Top 1 VariationKeyID from tInventoryWebPriceVariations where InventoryID = B.InventoryID ORDER BY price ASC) LEFT JOIN tInventory C on C.InventoryID = AA.VariationKeyID WHERE tInventoryWebData.WebFeaturedItem = 1 And tInventoryWebData.DisplayOnWebSite = 1 ORDER BY tInventoryWebData.WebTitle<------------
|
|
|
|
|
|