Table joining help please

[ameyemad]

Hello guys. I'm pulling my hair out over this one, and some help will def be appreciated. Here are the 2 tables:

 

table 1: prizes

idprize_namemax_winnerswin_type

1$15daily

2$22daily

3$51weekly

 

table 2: logs

idprize_idwin_time

111280494274

211280494300

331280493899

 

How can I make it so that I can join the 2 tables, using prize.id=log.prizeid, then choose a prize that has not reached maximum winners for the period (win_type).

 

Query which I have been working on which isn't working properly:

 

select prize.id,prize.prize_name,ifnull(county,0),tp.prize_id,prize.max_winners,tp.win_time,prize.win_type from prizes prize 
left join (
	select prize_id,win_time,count(*) as county from logs
) tp 
on prize.id=tp.prize_id where prize.max_winners>'0' and ((prize.win_type='once' and prize.max_winners>county) or (prize.win_type='hourly' and tp.win_time>'$_last_hr')) order by rand() limit 1

 

There is a few different "win_types"...

-once (which only gets awarded once)

-hourly

-daily

-weekly

 

I can get it to select a prize, but as soon as I add the "county" it stops working. I've tried so many methods to get this work but now it's time to throw in the towel and get some help please.

 

Thanks in advance. If you require more info please let me know.

[ameyemad]

After hours and hours spent on this I have a solution.