Geek asks and answers

OUTER JOIN with SQL for 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

Hair style

  - Id
  - ColorName
  - Description

I need to create a query that lists all the people in my database. Since MembershipStatusIdthey HairStyleIdare equal to zero, I know that I need to make a left outer join. However, since there are several tables, I am not sure how to do this. With a single left outer join, I know I can do this:

SELECT
  p.*,
  m.Name as 'MembershipStatus',
  -- how do i include the person 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 a left outer join for the name of the hair style. Can someone please tell me how to enable the complexion of a person?

Thank!

+3
source share
2 answers

LEFT JOIN, HairStyle

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

. SQL Fiddle

+9

, - :

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
+1

More articles:


All Articles