SQL - Mixing inner join with a left join

I have a SQL Server database with three tables: Gang, GangMember, and Neighborhood. These tables are structured like this:

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)

I need to get all gangs a gang member belongs to (yes they can belong to multiple). If there is a neighborhood, I need to return that as well. I want my result set to be in the form of: gang.name, neighborhood.name, member.position

So far, I've gotten here:

SELECT
 g.[Name],
 '' as 'Neighborhood' /* This what I don't kn开发者_如何学Cow how to do */
 m.[Position]
FROM
  [Gang] g, 
  [GangMember] m
WHERE
  m.[GangID]=g.[ID]

Because a gang may not have a neighborhood, I'm not sure how to complete this query. Can somebody help me out? Thanks!

Try this:

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]

select G.Name as GangName,
       N.Name as Neighborhood,
       GM.Name as MemberName,
       GM.Position as MemberPosition
from Gang as G
  inner join GangMember as GM
    on G.ID = GM.GangID
  left outer join Neighborhood as N
    on G.NeighborhoodID = N.ID

You can do the left join as follows:

SELECT 
 g.[Name], 
 n.name as 'Neighborhood' /* This what I don't know how to do */ 
 m.[Position] 
FROM 
  [Gang] g 
  INNER JOIN [GangMember] m ON m.[GangID]=g.[ID]
  LEFT JOIN Neighborhood n ON n.Id = g.NeighborhoodID

SELECT TOP (100) PERCENT
    DemographicTable.Name,
    ReferralTable.Code,
    ReferralTable.Date_Termed,
    ReferralTable.Referral_date,
    (
        SELECT Date_Accepted
        FROM CaseTable
        WHERE (ReferralTable.ID = DemographicTable.ID_Number)
            AND (Date_Accepted <> '')
            AND (DateTermed > '2013-11-01' OR DateTermed = '')
    ) AS Start,
    (
        SELECT DateTermed
        FROM CaseTable AS CaseTable_1
        WHERE (ReferralTable.ID = DemographicTable.ID_Number)
            AND (Date_Accepted <> '')
            AND (DateTermed > '2013-11-01' OR DateTermed = '')
    ) AS Term
FROM ReferralTable
INNER JOIN DemographicTable
    ON ReferralTable.ID = DemographicTable.ID_Number
    AND ReferralTable. Referral_date > '2012-12-01'
    AND ReferralTable. Code = 'JTC'
ORDER BY ReferralTable. Referral_date