CODEWITHSUNDEEP
mysqlsql
Ryan Ritten
Ryan Ritten

Reputation: 35

How can I query 2 tables and return results if 2nd table matches condition

I developed a game that has many tournaments that run. All the running tournaments are stored in the running_tournaments table. When a player enters a tournament, their score for that tournament is saved in the scores table.

I'd like to write a script that periodically checks the database to see if there are any tournaments that have ended (their end_date is in the past) AND all the scores for that tournament have been "verified". A score is considered verified when the verified value for that score is "1".

How can I query these tables to return all the tournament id's of the tournaments where all the scores for that tournament have been verified (verified = 1)?

As an example, given the data below, if today's date is January 3rd, the query should only return tournament id # 1. Tournament id 1 has two scores that have been verified and it's date is on or before today. All other tournaments are either in the future or have 1 or more scores that are not verified.

enter image description here

Upvotes: 0

Views: 33

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

Hmmm . . . I think join and group by does what you want:

select s.tourn_id
from scores s join
     running_tournaments rt
     on s.tourn_id = rt.id
where end_date <= curdate()
group by s.tourn_id
having min(s.verified) = 1;

Upvotes: 1

Related Questions