Browse by category
Browse all products
DECLARE @collectionIds1 int = 273;
DECLARE @collectionIds2 int = 281;
DECLARE @collectionIds3 int = 291;
DECLARE @collectionIds4 int = 292;
DECLARE @collectionIds5 int = 287;
DECLARE @collectionIds6 int = 286;
DECLARE @collectionIds7 int = 282;
DECLARE @collectionIds8 int = 294;
DECLARE @collectionIds9 int = 274;
DECLARE @collectionIds10 int = 295;
DECLARE @collectionIds11 int = 293;
DECLARE @collectionIds12 int = 288;
DECLARE @collectionIds13 int = 296;
DECLARE @collectionIds14 int = 276;
DECLARE @collectionIds15 int = 275;
DECLARE @collectionIds16 int = 285;
DECLARE @collectionIds17 int = 284;
DECLARE @collectionIds18 int = 289;
DECLARE @collectionIds19 int = 290;
DECLARE @collectionIds20 int = 279;
DECLARE @collectionIds21 int = 278;
DECLARE @collectionIds22 int = 277;
DECLARE @collectionIds23 int = 280;
DECLARE @collectionIds24 int = 283;
DECLARE @collectionIds25 int = 283;
DECLARE @collectionIds26 int = 283;
DECLARE @collectionIds27 int = 283;
DECLARE @collectionIds28 int = 283;
DECLARE @collectionIds29 int = 283;
DECLARE @collectionIds30 int = 283;
DECLARE @siteId int = 5;
DECLARE @GetValueOrDefault int = 10000;
SELECT [s1].[CollectionProductId], [s1].[CollectionId], [s1].[ProductId]
FROM (
SELECT DISTINCT [c].[CollectionId]
FROM [CollectionProduct] AS [c]
INNER JOIN [Collection] AS [c0] ON [c].[CollectionId] = [c0].[CollectionId]
INNER JOIN [Product] AS [p] ON [c].[ProductId] = [p].[ProductId]
WHERE [c].[CollectionId] IN (@collectionIds1, @collectionIds2, @collectionIds3, @collectionIds4, @collectionIds5, @collectionIds6, @collectionIds7, @collectionIds8, @collectionIds9, @collectionIds10, @collectionIds11, @collectionIds12, @collectionIds13, @collectionIds14, @collectionIds15, @collectionIds16, @collectionIds17, @collectionIds18, @collectionIds19, @collectionIds20, @collectionIds21, @collectionIds22, @collectionIds23, @collectionIds24, @collectionIds25, @collectionIds26, @collectionIds27, @collectionIds28, @collectionIds29, @collectionIds30) AND [c0].[SiteId] = @siteId AND [p].[SiteId] = @siteId AND [p].[IsDeleted] = CAST(0 AS bit)
) AS [s0]
INNER JOIN (
SELECT [s].[CollectionProductId], [s].[CollectionId], [s].[ProductId]
FROM (
SELECT [c1].[CollectionProductId], [c1].[CollectionId], [c1].[ProductId], ROW_NUMBER() OVER(PARTITION BY [c1].[CollectionId] ORDER BY CASE
WHEN [p0].[ProductType] = 'mn' THEN CAST(1 AS bit)
ELSE CAST(0 AS bit)
END DESC, [p0].[IsInStock] DESC, [p0].[DateTimeUpdated] DESC) AS [row]
FROM [CollectionProduct] AS [c1]
INNER JOIN [Collection] AS [c2] ON [c1].[CollectionId] = [c2].[CollectionId]
INNER JOIN [Product] AS [p0] ON [c1].[ProductId] = [p0].[ProductId]
WHERE [c2].[SiteId] = @siteId AND [p0].[SiteId] = @siteId AND [p0].[IsDeleted] = CAST(0 AS bit)
) AS [s]
WHERE [s].[row] <= @GetValueOrDefault
) AS [s1] ON [s0].[CollectionId] = [s1].[CollectionId]
