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
