Browse by category
Browse all products
DECLARE @collectionIds1 int = 80;
DECLARE @collectionIds2 int = 97;
DECLARE @collectionIds3 int = 297;
DECLARE @collectionIds4 int = 230;
DECLARE @collectionIds5 int = 70;
DECLARE @collectionIds6 int = 96;
DECLARE @collectionIds7 int = 82;
DECLARE @collectionIds8 int = 102;
DECLARE @collectionIds9 int = 75;
DECLARE @collectionIds10 int = 65;
DECLARE @collectionIds11 int = 68;
DECLARE @collectionIds12 int = 100;
DECLARE @collectionIds13 int = 99;
DECLARE @collectionIds14 int = 79;
DECLARE @collectionIds15 int = 78;
DECLARE @collectionIds16 int = 66;
DECLARE @collectionIds17 int = 76;
DECLARE @collectionIds18 int = 71;
DECLARE @collectionIds19 int = 77;
DECLARE @collectionIds20 int = 72;
DECLARE @collectionIds21 int = 74;
DECLARE @collectionIds22 int = 95;
DECLARE @collectionIds23 int = 81;
DECLARE @collectionIds24 int = 73;
DECLARE @collectionIds25 int = 98;
DECLARE @collectionIds26 int = 67;
DECLARE @collectionIds27 int = 101;
DECLARE @collectionIds28 int = 101;
DECLARE @collectionIds29 int = 101;
DECLARE @collectionIds30 int = 101;
DECLARE @siteId int = 2;
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]
