How to force SQL join conditions across multiple related rows?

Question

Suppose you have a database schema with tournaments related to games. There can be many games in a tournament.

I am trying to join tournaments for games and only retreat from tournaments with all games in the future.

SELECT DISTINCT tournaments.*
FROM tournaments
INNER JOIN games ON tournaments.game_id = games.id
WHERE games.event_date >= NOW();

There are a few more tables that I join, but I simplified this for the sake of this example.

My query returns results when not all games in the tournament in the future.

I also tried moving the condition to the connection:

SELECT DISTINCT tournaments.*
FROM tournaments
INNER JOIN games ON (tournaments.game_id = games.id AND games.event_date >= NOW())

but I get the same result.

How can I make sure that all tournaments won will have games in the future - that is, a mandatory condition for all lines related to?

Thank!

Answer 1

, MySQL. - , , , .

SELECT * from tournaments
WHERE id IN
( SELECT tournaments.id
  FROM tournaments INNER JOIN games ON tournaments.game_id = games.id
  GROUP BY tournaments.id
  HAVING MIN(games.event_date) >= now()
)

Answer 2

SELECT DISTINCT tournaments.*
    FROM tournaments
        INNER JOIN games 
            ON tournaments.game_id = games.id
                AND games.event_date >= NOW()
    WHERE NOT EXISTS (SELECT NULL FROM games g2 WHERE g2.id = tournaments.game_id AND g2.event_date < NOW())

Answer 3

maybe something like this:

SELECT DISTINCT t.* 
FROM tournaments t, (select id, event_date from games where event_date >= now() ) g
where t.game_id = g.id 

Answer 4

try it

SELECT DISTINCT *
FROM tournaments 
where game_id in (
Select id from games where event_date >= NOW()
)