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!

Both of your joins are on primary keys, so as per the answers so far, its fairly straightforward. (Outer joins start to get tricky one you have two or more X-to-many outer joins to deal with.) — Philip Kelley, May 14 '12 at 13:48

Taryn · Accepted Answer · 2012-05-14T13:56:25.470

10

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

edited May 14 '12 at 13:56

answered May 14 '12 at 13:44

Taryn

242,637
56
362
405

What happens if MembershipId is null, but HairStyleId is not? Will it still work? In my mind, the query would quit if MembershipStatusId was null. But, I might be misunderstanding something. – JavaScript Developer May 14 '12 at 13:47
Since it is an outer join you should still get the result even if it is null, I updated my answer with a sql fiddle demo – Taryn May 14 '12 at 13:56

score 1 · Answer 2 · answered May 14 '12 at 13:44

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

OUTER JOIN with SQL across multiple tables in SQL Server 2008

2 Answers2