Created
January 5, 2015 19:26
-
-
Save AndyConlisk/a28123f8616efc1b1205 to your computer and use it in GitHub Desktop.
SQL to get a random image from a product of the product category
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT b.Id AS BusinessId, COALESCE(subCategoryImage.Id, parentCategoryImage.Id) AS ImageAssetId, COALESCE(subCategoryImage.CategoryId, parentCategoryImage.CategoryId) AS CategoryId, b.Name, COALESCE(subCategoryImage.FileName, parentCategoryImage.FileName) AS FileName | |
FROM Businesses b | |
OUTER APPLY ( | |
SELECT TOP 1 * | |
FROM ImageAssets | |
WHERE CategoryId = b.CategoryId | |
AND Deleted = 0 | |
ORDER BY CHECKSUM(NEWID(), b.id) & 2147483647 | |
) subCategoryImage | |
OUTER APPLY ( | |
SELECT TOP 1 * | |
FROM ImageAssets | |
WHERE Deleted = 0 | |
AND CategoryId IN ( | |
SELECT Id | |
FROM Categories | |
WHERE ParentCategoryId = b.CategoryId) | |
ORDER BY CHECKSUM(NEWID(), b.id) & 2147483647 | |
) parentCategoryImage | |
WHERE b.Id IN @businessIds |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment