Skip to content

Instantly share code, notes, and snippets.

@AndyConlisk
Created January 5, 2015 19:26
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save AndyConlisk/a28123f8616efc1b1205 to your computer and use it in GitHub Desktop.
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
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