title: SQL - Mixing inner join with a left join
body: <p>I have a SQL Server database with three tables: Gang,  GangMember, and Neighborhood. These tables are structured like this:</p>
  
  <pre><code>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)
  </code></pre>
  
  <p>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</p>
  
  <p>So far, I've gotten here:</p>
  
  <pre><code>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]
  </code></pre>
  
  <p>Because a gang may not have a neighborhood, I'm not sure how to complete this query. Can somebody help me out? Thanks!</p>