Selecting random top 3 listings per shop for a range of active advertising shops
- by GraGra33
I’m trying to display a list of shops each with 3 random items from their shop, if they have 3 or more listings, that are actively advertising. I have 3 tables: one for the shops – “Shops”, one for the listings – “Listings” and one that tracks active advertisers – “AdShops”.
Using the below statement, the listings returned are random however I’m not getting exactly 3 listings (rows) returned per shop.
SELECT AdShops.ID, Shops.url, Shops.image_url, Shops.user_name AS shop_name,
Shops.title, L.listing_id AS listing_id, L.title AS listing_title,
L.price as price, L.image_url AS listing_image_url, L.url AS listing_url
FROM AdShops INNER JOIN
Shops ON AdShops.user_id = Shops.user_id INNER JOIN
Listings AS L ON Shops.user_id = L.user_id
WHERE (Shops.is_vacation = 0 AND Shops.listing_count > 2 AND
L.listing_id IN
(SELECT TOP 3 L2.listing_id
FROM Listings AS L2
WHERE L2.listing_id IN
(SELECT TOP 100 PERCENT L3.listing_id
FROM Listings AS L3
WHERE (L3.user_id = L.user_id)
)
ORDER BY NEWID()
)
)
ORDER BY Shops.shop_name
I’m stumped. Anyone have any ideas on how to fix it?
The ideal solution would be one record per store with the 3 listings (and associated data) were in columns and not rows – is this possible?
