I'm not a MYSQL guru, but I'll try to help:

For #1

Solution seems obvious at first glance, but everything I tried didn't work as expected. There's DISTINCT and GROUP BY which shoud limit the number of returned rows, but DISTINCT cannot be applied on a single column: it is applied on the entire row. GROUP BY need to be placed before ORDER BY rand, so it returns the pictures, and then randomize the result. Not good either.

If you're coding in PHP or something else, you could do:

SELECT DISTINCT id from groupname where exclude=0 ORDER BY rand();

which will return all the groupid you want, without repetition. Then loop in the PHP or whatever you're using and do:

SELECT groupname.Group, pictures.Thumb, groupname.ID, groupname.newgroup
FROM rascal.pictures, rascal.groupname
WHERE pictures.Exclude=0 AND pictures.groupnameid = "prevously_fetched_groupid_scrolled_by_loop" and Thumb <> ''
ORDER BY RAND() LIMIT 1;

Which should return 1 picture each time this query is called: for each group.


Sure, this isn't nice, but I looked on google and I just can't find anything better.

Ah, and if you have MySQL 4.1 or later, it seems you could try something like (not verified here):

select *
from (
SELECT groupname.Group, pictures.Thumb, groupname.ID, groupname.newgroup
FROM rascal.pictures, rascal.groupname
WHERE pictures.Exclude=0 AND groupname.exclude=0 AND pictures.groupnameid = groupname.id and Thumb <> ''
ORDER BY RAND()
)
GROUP BY groupname.id;