Welcome,
 
 


select isNull( pca.SortOrder, 10000) 'SortOrder', a.ProductID, DateCreated, SalePrice, ImageID, ImageStoreID, ProductName, ModelNumber, CategoryName, b.CategoryID, parentid, pieces_order_min, ReorderLevel, totalpieces , VirtualProduct, isNull( IgnoreInventoryRules, 0) 'IgnoreInventoryRules', isNull(availableQuantities, '') 'AvailableQuantities', av.caption 'availability', isNull(hasOption, 0) 'hasOption', isNull( inCart, 0) 'InCart', case when isNull(ignoreinventoryrules, '0') = '1' AND (isNull( SizeValues, '') > '' OR isNull( StyleValues, '') > '') then 1 when isNull(RequiresSizeColorStyle, 0) > 0 then 1 else 0 end 'RequiresSizeColorStyle', convert( varchar(2500), isNull( ProductDescription, '') ) 'ProductDescription' FROM vw_Products a left join (select productid, sum(quantity) 'inCart' from tblCartDetails where isNull( groupitemownerid, 0) = 0 and CartID=194806 group by productid ) thecart on a.productid = thecart.productid left join vw_ProductExtendedInventoryOptionFlag PEI on a.productid = PEI.productid inner join ( select * from tblCategoryProductBridge where categoryid IN( SELECT CategoryID FROM tblcategorysecurity WHERE userid = 0 ) OR CategoryID IN(select categoryid from tblProdCategories where showanonymous=1) ) b on a.productid=b.productid inner join tblProdCategories c on b.categoryid=c.categoryid left join tblAvailability av on a.availabilityid = av.availabilityid left join (select productid, propertyValue 'AvailableQuantities' from ut_ExtendedProductProperties where propertyName = 'availableQuantities') AQS on a.productid = AQS.productid left join (select productid, min(sortorder) 'sortorder' from ut_ProductCategorySort where categoryid =11 group by productid) pca on a.productid=pca.productid WHERE 1 = 1 and c.display=1 AND Saleprice > 0 AND showanonymous=1 AND (TotalPieces>0 OR a.ProductID IN(select productid from vw_ExtendedAllowedProducts) ) ORDER BY isNull(pca.sortorder,0), ProductName, ModelNumber, a.DateCreated