Outer Join help

Ok...I have 3 tables.

Entity
---
name
entity_key

Address
----
street
zip
mailing_flag
entity_key

Phone
---
phone_number
phone_type_key
entity_key

I want to see all of the Entity records with their corresponding Address and Phone records. (select e.name, a.street, a.zip, p.phone_number)But only show the Address record for that Entity if the mailing_flag is 'Y' and I only want to see the Top 1 Phone record where the phone_type_key = 'Home'. If the above criteria isn't met I just want to see nulls for the Address and Phone records.

My problem is getting ALL the Entity records to return. It only wants to give me the Entity records that have Address or Phone associated with them. That and somehow showing the Top 1 phone record for the Entity are my issues.

Any help would be much appreciated.....Thanks!This sounds suspiciously like an incomplete homework assignment. Are you holding out part of the specification?

-PatP|||Originally posted by Pat Phelan
This sounds suspiciously like an incomplete homework assignment. Are you holding out part of the specification?

-PatP

--Nope, that's all I need. Maybe an outer join won't work, I don't know, that's why I'm asking for help.

Steve|||Ok, then rolling them all together I get:SELECT e.*
FROM dbo.entity AS e
JOIN (SELECT TOP 1 *
FROM dbo.phone
WHERE 'Home' = phone_type_key) AS p
ON p.entity_key = e.entity_key)
JOIN (SELECT TOP 1 a.*
FROM dbo.Address
WHERE 'Y' = mailng_flag) AS a
ON (a.entity_key = e.entity_key)-PatP