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.
FROM dbo.entity AS e
JOIN (SELECT TOP 1 *
WHERE 'Home' = phone_type_key) AS p
ON p.entity_key = e.entity_key)
JOIN (SELECT TOP 1 a.*
WHERE 'Y' = mailng_flag) AS a
ON (a.entity_key = e.entity_key)