我正在做一些与这个SO线程(链接(非常相似的事情。不同之处在于我的表有时具有空值。这是我提供的链接下面的插图。
1( 包含自己喜欢运动的人员列表的"人"表
ID | Name | FavSport | DislikeSport
------------------------------------
1 | Bob | 1 | 5
2 | Roy | 3 | 4
3 | Sarah | |
4 | Kevin | 2 |
5 | Mary | | 3
2( 显示运动列表的表格"运动">
ID | Sport
---------------
1 | Football
2 | Basketball
3 | Volleyball
4 | Soccer
5 | Badminton
我从我在此线程中发布的链接中获得的查询
select people.Name, sports.Sport
from people, sports
where people.FavSport = sports.ID
3(结果如果查询类似上面
Name | Sport | Dislike
----------------------------
Bob | Football | Badminton
Roy | Volleyball | Soccer
Sarah | Football | Football
Kevin | Basketball | Football
Mary | Football | Volleyball
4(我想要的是空的,如下所示(或任何默认值,如:无(
Name | Sport | Dislike
----------------------------
Bob | Football | Badminton
Roy | Volleyball | Soccer
Sarah | |
Kevin | Basketball |
Mary | | Volleyball
有人如何处理这个问题吗?提前谢谢。
你需要一个外部连接
select people.Name,
likes.Sport as FavSport ,
dislikes.Sport as DislikeSport
from people
left join sports likes
on people.FavSport = likes.ID
left join sports dislikes
on people.DislikeSport = dislikes.ID
加入的标准文档
SELECT people.NAME,
Isnull(sport.sport, '') AS Sport
FROM people
LEFT JOIN sport
ON favsport = sport.id
你需要做一个left outer join
。
select people.Name, sports.Sport
from people
left join sports on (people.FavSport = sports.ID);