SQL query with associated column

user_2557256 0
Viewed 143

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!

mysql sql database
3 Answers

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

You can do as shown below:

    SELECT 
    s.id,
    s.game_date,
    s.game_time,
    s.home_team_id,
    homeTeam.abbreviated_name,
    s.visitor_team_id,
    visitorTeam.abbreviated_name
    
    FROM schedule s
    left join team homeTeam on homeTeam.id=s.home_team_id
    left join team visitorTeam on visitorTeam.id=s.visitor_team_id      
   -- where homeTeam.abbreviated_name ='ATL' OR visitorTeam.abbreviated_name='ATL'

result

user_119873960 
SELECT *
FROM schedule s
JOIN team t ON t.id IN (s.home_team_id, s.visitor_team_id)
WHERE t.abbreviated_name = 'ATL'

If you need abbreviated names for both commands then use, for example

SELECT s.*, t1.abbreviated_name, t2.abbreviated_name
FROM schedule s
JOIN team t1 ON t1.id = s.home_team_id
JOIN team t2 ON t2.id = s.visitor_team_id
WHERE 'ATL' IN (t1.abbreviated_name, t2.abbreviated_name)
user_101387340
Related