Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
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