SQL - 混合内部连接和左连接

Question

我有一个包含三个表的 SQL Server 数据库:Gang、GangMember 和 Neighborhood。这些表的结构如下:

Neighborhood
------------
ID (non-null)
Name (non-null)

Gang
----
ID (non-null)
Name (non-null)
NeighborhoodID (nullable)

GangMember
----------
ID (non-null)
GangID (non-null)
Name (non-null),
Position (nullable)

我需要获取一个帮派成员所属的所有帮派(是的，他们可以属于多个)。如果有邻居，我也需要归还它。我希望我的结果集采用以下形式:gang.name, neighborhood.name, member.position

到目前为止，我已经到了这里:

SELECT
 g.[Name],
 '' as 'Neighborhood' /* This what I don't know how to do */
 m.[Position]
FROM
  [Gang] g, 
  [GangMember] m
WHERE
  m.[GangID]=g.[ID]

因为帮派可能没有邻居，所以我不确定如何完成此查询。有人可以帮帮我吗？谢谢!

Answer 1

试试这个:

SELECT
  g.[Name],
  n.[Name] as 'Neighborhood',
  m.[Position]
FROM 
  [Gang] g INNER JOIN [GangMember] m ON m.[GangID]=g.[ID]
  LEFT OUTER JOIN [Neighborhood] n ON g.[NeighborhoodID] = n.[ID]