Select Random User for Contest

[XtReMaTriX]

Hey,
I have been trying to figure out how to do this. I have a contest going on in my forum Win a Cooler Master ATC-710 GX1 Case (http://www.xtremods.com/vbulletin/showthread.php?s=&threadid=501) and i need to select a user with 15 or more posts and who has started at least 2 threads. Is there a way to randomly select this user? Has anyone else needed to do this???

[filburt1]

With MySQL 4 it would be easy with subselects but here's a MySQL 3 way. It returns the userid and username of one user who has both made 15 posts and created one thread. I don't know how to modify it to use two other than using subselects (MySQL 4).

SELECT u.username, u.userid FROM user u, thread t
WHERE u.posts > 15 AND t.postuserid = u.userid
ORDER BY RAND() LIMIT 1;

[Xenon]

hmm, not sure, but i think this can work for two thread
(it's not a nice way but should do it:)

SELECT u.username, u.userid FROM user u, thread t, thread t2
WHERE u.posts > 15 AND t.postuserid = u.userid AND t2.postuserid=u.userid AND t.threadid!=t2.threadid
ORDER BY RAND() LIMIT 1;

[filburt1]

Yeesh that's filthy but it looks like it should work. :)

BTW both queries are really server-intensive so don't run them too often. My iBook's load average right now is 0.48 and it took 1.5 seconds to run the query using a week or two old WDF database dump.

[XtReMaTriX]

wow, thanks guys... very fast reply... looks like it will work GREAT! i am going to test it out now...