[RESOLVED] Would this be a left outer join?

Dolemite50

Hi,

I'm still getting my feet wet on JOIN and am wondering how to do this:
On my site there are weekly prizes for high scores. If you win a prize, your userid is pulled from the high_scores table and put into the prize_winners table, along with the date as "prize_date". Only members who have not won a prize within the past two months are eligible to win again.

So,..my query would need to select the top 6 scorers' userid from high_scores, but only if their userid is not already in the prize_winners table with a prize_date < NOW-2mos.

Thanks for any help.

..I should add that subqueries are not an option.

Roger_Ramjet

2 queries is the only way:

1 extract ther user ids into a work table with a create table query

2 left join to that table with is null to elliminate them from the potential winners

Dolemite50

Thanks Roger.

I think I see where you're going. Hmmm,..well I can't use "LIMIT 6", or I would have to run additional queries if one of the 6 were ineligible. If I remove the limit I'll have to dump 25k+ items into a temp table every time, so what do you do in that situation? Just grab a reasonable amount like 50 or so and take it from there?

Dolemite50

..or maybe just do a query for the top 50 userids from high_scores and run a loop like:
while (i <= 6){
SELECT * from prize_table WHERE userid=userid AND prize_date < NOW - 2mos..
if(!mysqlnumrows){
enter into prize_table...
i++;
}

In other words,..just like you said, two queries. I'm pretty surprised that there isn't some sort of JOIN to handle that but "whadaya do"? 🙂

Thanks again.

Roger_Ramjet

No. Create a work table with the CREATE ..... SELECT syntax, and in the select part you select all the users from the prize winners table who are ineligible. You now have a table you can LEFT JOIN to and use IS NULL to elliminate them from the prize draw in the second query. Just 2 queries and no loops.

Dolemite50

Oh man, I missed this reply. Thanks again.

Well since the last posting I've switched things up a bit and now it just deletes entries from the table > 3mos old before I start the queries. Anybody who isn't in that table is eligible. How would you adjust for that?

Thanks again.

nnichols

This should satisfy your original requirement without having to delete the previous entries. Give it a try and let us know how you get on. My SQL is a little rusty at the moment but I think it will do what you're after.

SELECT {field list}
FROM high_scores hs
LEFT JOIN prize_winners pw ON hs.userid = pw.userid
WHERE pw.userid IS NULL OR pw.prize_date < DATE_SUB(CURDATE(), INTERVAL 2 MONTHS)
ORDER BY {pw.score_field} DESC
LIMIT 6

The bits in {} need changing to the relevant field names