SQL outer join

Question

I've got 3 tables. The SQL below should explain a little of the data in there that I need:

SELECT m.FirstName, m.LastName, m.DOB, m.Gender, a.Address1, a.Address2, a.City, 
a.State, a.Country, a.PostCode from Members m 
LEFT JOIN Addresses a ON m.Id = a.MemberId 
INNER JOIN AddressType at ON at.Id = a.AddressTypeId 
WHERE m.Email = 'member2@test.com' AND at.Type = 'Account'

If data is present in the Members for the user but not in the addresses I still need the FirstName, LastName etc to be returned but the Address etc as NULL. Currently it's all coming back as null. I thought I could do this with an outer join but I've tried and can't get it to pull what I need back. Am I thinking completely wrong or just doing it wrong? And yes, if I'm doing it wrong I'd love it if you could tell me what and how. :)

EDIT:

I made the modification suggested but:

SELECT m.FirstName, m.LastName, m.DOB, m.Gender, a.Address1, a.Address2, a.City, 
a.State, a.Country, a.PostCode from Members m 
LEFT JOIN Addresses a ON m.Id = a.MemberId 
LEFT JOIN AddressType at ON at.Id = a.AddressTypeId 
WHERE m.Email = 'member2@test.com' AND at.Type = 'Account'

this doesn't work. When I remove the AddressType:

SELECT m.FirstName, m.LastName, m.DOB, m.Gender, a.Address1, a.Address2, a.City, 
a.State, a.Country, a.PostCode from Members m 
LEFT JOIN Addresses a ON m.Id = a.MemberId 
WHERE m.Email = 'member2@test.com' 

it pulls back the data so I know the first join is working. Any ideas?

Answer 1

You need to use LEFT JOIN on both joins. Right now the first join acts as you want it to, but the following INNER JOIN tries to match the at.Id = a.AddressTypeId condition on a NULL and it can't, so the row isn't returned.

An INNER JOIN will return a non-empty, non-NULL result only if it can satisfy its ON condition.

Answer 2

Try changing the INNER JOIN on AddressType to a LEFT JOIN as well, at the moment I think the INNER JOIN will be enforcing that fields exist in both tables