mysql - SQL query with associated column

Question

I have the following tables:

Team

id | abbreviated_name 
----------------------
1  | ATL
2  | BOS
3  | BRK

Schedule has two foreign keys home_team_id and visitor_team_id

id | game_date  | game_time | home_team_id | visitor_team_id
------------------------------------------------------------
1  | 2021-01-01 | 7:00p ET  | 1            | 2
2  | 2021-01-02 | 6:00p ET  | 2            | 3
3  | 2021-01-03 | 7:00p ET  | 1            | 3

How do I query for all the rows in Schedule given a team abbreviated name? Say I want to find all the rows where ATL is playing both home and away games. I tried the following but the resulting dataset is way off.

SELECT *
FROM schedule s
JOIN team t
WHERE s.home_team_id = (
    SELECT id
    FROM team
    WHERE team.abbreviated_name = 'ATL'
)
OR s.visitor_team_id = (
    SELECT id
    FROM team
    WHERE team.abbreviated_name = 'ATL'
)

Appreciate the help!

question from:https://stackoverflow.com/questions/65896109/sql-query-with-associated-column