First thing you need to do is to read the sticky about RAND() at the top of this forum.
So the first thing to ask is why does it need to be a random image for the property? Surely the user needs to see an image that shows the whole property from the front first, rather than a random image of the kitchen, or a bedroom, etc. In which case you would just designate 1 image for each property as the main image and then a simple join with that main image would be all you need.
If you insist on random images then you will need some complex subquerying.
First get a sequence number for each image for each property and save as view1
(if your server does not support views then upgrade it to MySQL 5.1 + or change hosting to one that does)
SELECT t1.auctionpropertyid AS p, t1.auctionpropertyimagesid AS i, COUNT(t1.auctionpropertyimages) AS c, t1.auctionpropertyimagesid >= t2.auctionpropertyimagesid AS flg
FROM auctionpropertyimages AS t1, auctionpropertyimages AS t2
GROUP BY p, i
HAVING flg = TRUE
Now you have a sequence no for each property image, but we also need the number of images for each property; call this view2
SELECT auctionpropertyid AS p, COUNT(auctionpropertyimagesid) as m
FROM auctionpropertyimages
GROUP BY p
That will give you the image count for each property.
Next you will need to combine these two to get the sequence and the count for each property image, save as view3
SELECT p, i, c, m
FROM view1 INNER JOIN view2 USING(p)
That will give you propertyid, imageid, sequence no and count for all images
Now you can use the sequence and count to control RAND(), save as view4
SELECT p, i
FROM view3
WHERE c = FLOOR(1 + RAND() * (m - 1))
That give you a random image id for each property and you can join to it to get all the columns you need. It will also be slow as a boat to run 😃 so it is an interesting exercise rather than a practical solution.
You can of course speed processing up by using an INSERT TRIGGER to calculate and store the sequence no against each image and the count against each property using the first few views. Still be slow but you will insert images far less often than you will display them.
You will then only need the WHERE part of the last query included in your general select thus.
SELECT * ,LEFT(description ,30) As description
FROM auctionpropertyaddressdata LEFT JOIN auctionpropertyimages USING(auctionpropertyid)
WHERE imagesequence = FLOOR(1 + RAND() * (imagecount - 1))
ORDER BY propertyprice ASC
This will not suffer from all the drawback of using ORDER BY RAND() as we are using RAND here for its intended purpose, ie to generate a random number.
PS. as I said, if you can't use triggers then change your server and or host to one that does.
