OUTER JOIN with SQL across multiple tables in SQL Server 2008

Question

I have a SQL Server 2008 database. This database has three tables:

Person

  - Id
  - FullName
  - MembershipStatusId (nullable)
  - HairStyleId (nullable)

MembershipStatus

  - Id
  - Name

HairStyle

  - Id
  - ColorName
  - Description

I need to create a query that lists all of the people in my database. Because MembershipStatusId and HairStyleId are both nullable, I know I need to do a left outer join. However, because there are multiple tables, I'm not sure how to do it. With a single left outer join, I know I can do this:

SELECT
  p.*,
  m.Name as 'MembershipStatus',
  -- how do i include the person's hair color as part of my result set?
FROM
  Person p LEFT OUTER JOIN
    MembershipStatus m ON p.[MembershipStatusId]

However, I'm not sure how to add the left outer join for the hair style name. Can somebody please tell me how to include the Person's hair color?

Thank you!

Answer 1

You just do another LEFT JOIN to include the HairStyle Table

SELECT
  p.*,
  m.Name as 'MembershipStatus',
  h.ColorName
FROM Person p 
LEFT JOIN MembershipStatus m 
    ON p.[MembershipStatusId] = m. Id
LEFT JOIN HairStyle h
    ON p.HairStyleId = h.id

See a demo on SQL Fiddle

Answer 2

Maybe something like this:

SELECT
    Person.id,
    MembershipStatus.Name AS MemberShip,
    HairStyle.ColorName AS HairStyleColorName
FROM
    Person
    LEFT JOIN MembershipStatus
        ON Person.MembershipStatusId=MembershipStatus.Id
    LEFT JOIN HairStyle
        ON Person.HairStyleId = HairStyle.Id