Need some MySQL help please

[Unknown553]

I've set up a referral contest on my forum and recently we cought someone cheating and registering multiple accounts, so we set up a new rule; referrals only count after they post 10 posts.

So I have a leaderboard set up which is almost the exact code from the admin CP (user.php?action=referrers more specifically). It shows me how many referrals have been aquired for referrers in a specific time period. But now how do I make it so that it only shows me the referred members with 10+ posts?

The query I have now is

[sql]$users = $DB_site->query("SELECT COUNT(*) AS count, user.username, user.userid FROM user AS users
LEFT JOIN user ON (users.referrerid = user.userid)
WHERE users.referrerid <> 0
$datequery
GROUP BY users.referrerid
ORDER BY count DESC");[/sql]

It doesn't show up, but in that blank space between WHERE and GROUP BY is the date query, currently a variable right now.

Any ideas? Thanks in advance.

[Xenon]

this should be enough:

$users = $DB_site->query("SELECT COUNT(*) AS count, user.username, user.userid FROM user AS users
                   LEFT JOIN user ON (users.referrerid = user.userid)
                   WHERE users.referrerid <> 0 AND user.posts > 9
                   
                   GROUP BY users.referrerid
                   ORDER BY count DESC");

[Unknown553]

Well first off you didn't select the posts row in the query before using it, and second, I tried that. The problem is that I'm trying to count only the referrALS that have 10+ posts, not the referrERS that have 10+ posts. The query you posted only displays the referrERS with 10+ posts. Thanks but it doesn't work.

[Unknown553]

Anyone? Please?