Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
516 views
in Technique[技术] by (71.8m points)

mysql - SQL query with associated column

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

Please log in or register to answer this question.

Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...