OUTER JOIN with SQL across multiple tables in SQL Server 2008

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!

2 Answers

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

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