I am using mysql 4.0.25.

I have selected an image from table products to display with each category from table cat2. There is more than one image(product) that can be selected and I would like randomize the image(product) selection but I cant seem to get what I want.

What I would like to do is to select a single (randomised) image from table products related to each category in table cat2 which is related to the selected category in table cat1

Below are the table structures and query I am using at the moment. It selects unique cat2 but always the same image(product).

cat1 (cat1_id , cat1_display , cat1_url)
cat2 (cat2_id , cat2_display , cat2_url)
products (prod_id , image_small , display)
cat_join (cat1_id , cat2_id , prod_id)

This is my basic query:

SELECT cat2_display,cat2_url,prod_id,image_small
FROM cat1,cat2,cat_join,products
WHERE cat1.cat1_id=cat_join.cat1_id
AND cat2.cat2_id=cat_join.cat2_id
AND products.prod_id=cat_join.prod_id
AND products.display = 'yes'
AND cat1_url = '$var_cat1_url'
GROUP BY cat_join.cat2_id
ORDER BY cat2.cat2_display ASC

I also tried variations of the next query without success

SELECT cat2_display,cat2_url,prod_id,image_small,image_sm all*0+RAND() AS randNum
FROM cat1,cat2,cat_join,products
WHERE cat1.cat1_id=cat_join.cat1_id
AND cat2.cat2_id=cat_join.cat2_id
AND products.prod_id=cat_join.prod_id
AND products.display = 'yes'
AND cat1_url = '$var_cat1_url'
GROUP BY cat_join.cat2_id
ORDER BY cat2.cat2_display ASC,randNum


Can anyone help please.

Thanks