CODEWITHSUNDEEP
mysqlsql
user16251889
user16251889

Reputation: 1

SQL - search multiple columns from multiple tables?

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

Upvotes: 0

Views: 443

Answers (2)

Preeti
Preeti

Reputation: 62

    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. enter image description here

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522762

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';

Upvotes: 0

Related Questions