时间戳

首页 > 解决方案 > 如果几乎一个内部连接条件没有值，如何返回 null？

mysql - 如果几乎一个内部连接条件没有值，如何返回 null？

问题描述

我有一个名为的表league_ranking，其中包含一个特定round于teams一个competition. 现在发生 somerounds在表中没有任何值league_ranking，所以在这种情况下，我想防止在出现的列表中competitions出现competition几乎round没有值的league_ranking.

这是我的查询：

SELECT c.name AS competition_name,
  c.id AS competition_id
  FROM competition c
  INNER JOIN competition_seasons s ON s.competition_id = c.id
  INNER JOIN competition_rounds r ON r.season_id = s.id
  INNER JOIN league_ranking l ON l.round_id = r.id
  WHERE c.country_id = :country_id
  GROUP BY c.id
  ORDER BY c.name ASC

数据示例和表结构

联赛排名

|position | team_id | round_id | 
     1        120        5
     2        124        5

比赛回合

| id | season_id | name 
   5       577      First Round
   6       578      Preliminary Round

比赛季节

|id | competition_id
 577      28
 578      28

竞赛

|id | name 
  28  Premier

正如你所看到的round6 里面没有值league_ranking，问题是我的查询甚至返回了竞争Premier，我怎样才能阻止返回competition几乎round没有值的这个？

谢谢。

标签： mysqlsqlpdo

解决方案

您可以尝试使用LEFT JOIN代替INNER JOIN，您需要Outer JOIN基于competition_rounds表。

SELECT c.name AS competition_name,
c.id AS competition_id
FROM  competition_rounds r 
LEFT JOIN competition_seasons s ON r.season_id = s.id
LEFT JOIN competition c ON s.competition_id = c.id
LEFT JOIN league_ranking l ON l.round_id = r.id
WHERE c.country_id = :country_id
GROUP BY c.id
ORDER BY c.name ASC

sqlfiddle

推荐阅读