sql - 从连接的表中获取数据

Question

我有两个这样的表。

create table teams (
  "ID" Integer NOT NULL ,
  "STADIUM_ID" Integer NOT NULL ,
  "NAME" Varchar2 (50) NOT NULL ,
    primary key ("ID") 
    ) ;

create table matches (
  "ID" Integer NOT NULL ,
  "HOMETEAM_ID" Integer NOT NULL ,
  "OPPONENT_ID" Integer NOT NULL ,
  "HOMESCORE" Integer,
  "OPPONENTSCORE" Integer,
primary key ("ID","HOME_ID","OPPONENT_ID") 
) ;

他们有以下数据:

select * from matches;

ID      HOME_ID OPPONENT_ID HOMESCORE OPPONENTSCORE
 1          5           2           5             2
 2          4           5           1             0
 3          3           2           1             0
 4          3           2           1             0
 5          1           2           2             0
 6          3           1           2             1

select * from teams;

ID     STADIUM_ID    NAME
 1          1        Team1
 2          3        Team2
 3          4        Team3
 4          2        Team4
 5          5        Team5

我需要找到在主场输掉比赛的球队(与获胜者具有相同的 stadiumid)。真的appriciate你的帮助......预期结果 - 此数据没有，因为每个团队都有自己的体育场。

有些查询不起作用，但会显示我想要得到的内容。

select op.name from matches, (select name, stadium_id from teams where id = matches.home_id) home, (select name, stadium_id from teams where id = matches.opponent_id) op where home.stadium_id = op.stadium_id;

此查询中有两个问题 - 我没有比较分数来确定输球的球队，而且我无法像这样访问子查询中的匹配项。

Answer 1

试试这个解决方案:

select m.*,h.NAME as Home,o.NAME as Opponent from matches as m
Join teams h on h.STADIUM_ID = m.HOMETEAM_ID
Join teams o on o.STADIUM_ID = m.OPPONENT_ID
where m.HOMESCORE < m.OPPONENTSCORE