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

Answer 1

Your subquery is correct, but when same abbreviated name has more than one id's it returns more than one row, which will give error. Example:

id | abbreviated_name 
----------------------
1  | ATL
4  | ATL

in satisfies this case. Also join is not needed when using sub query, which will create extra records when join on conditions don't match

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

This is join version

SELECT *
FROM schedule s
LEFT JOIN team home_t on s.home_team_id=home_t.id
LEFT JOIN team visitor_t on s.visitor_team_id=visitor_t.id
WHERE home_t.abbreviated_name = 'ATL'
OR visitor_t.abbreviated_name = 'ATL'