How do I select all the team from USA that have a match (participate) after '2022-01-02' ?
event table.
| home_team | away_team | date |
|---|---|---|
| AAA | BBB | 2022-01-01 |
| AAA | FFF | 2022-01-01 |
| BBB | CCC | 2022-01-02 |
| AAA | HHH | 2022-01-05 |
| RRR | AAA | 2022-01-05 |
| QQQ | BBB | 2022-01-09 |
stat table.
| team_name | country | match_played |
|---|---|---|
| AAA | USA | 49 |
| BBB | France | 22 |
| CCC | Canada | 32 |
| DDD | USA | 25 |
| EEE | Italy | 20 |
| FFF | Germany | 90 |
| GGG | France | 62 |
| HHH | USA | 25 |
CodePudding user response：
Join the event table to the stat table twice, and then check the match date:
SELECT DISTINCT
CASE s1.country WHEN 'USA'
THEN e.home_team ELSE e.away_team END AS team
FROM event e
INNER JOIN stat s1 ON s1.team_name = e.home_team
INNER JOIN stat s2 ON s2.team_name = e.away_team
WHERE s1.country = 'USA' OR s2.country = 'USA' AND
e.date > '2022-01-02';
CodePudding user response：
SELECT team_name FROM `tb2` WHERE `country` = 'USA'
AND team_name IN
(SELECT home_team FROM tb1 WHERE date > '2022-01-02'
UNION
SELECT away_team FROM tb1 WHERE date > '2022-01-02')
First, we fetch the team name who have a match (participate) after '2022-01-02' then select the USA team only.
