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 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…