Multi Table Join with WHERE

I have 3 tables. A contact, email and phone table, with the phone and email tables having a contactID reference. I am looking to yield a result set that would have the contact information for everyone, plus their office phone and office email (phoneType = Office AND emailType = Office) IF they have one.

Basic Query Attempted
SELECT c.ContactID, c.Name, p.Phone, e.Address FROM contacts c LEFT OUTER JOIN email e ON c.ContactID = e.ContactID LEFT OUTER JOIN phone p ON c.ContactID = p.ContactID WHERE p.type = 'Office' and e.type = 'Office'

Unfortunately this filters out those contacts that don't have an Office Phone and Office Email. I am looking to get a result where I have those records with NULL in the Phone and Email columns.

I am new to stored procedures and am not fully sure of the capabilities I have there, so any guidance would be helpful.

Thanks

Hi,

Just move the type conditions to join conditions from the overall where condition.

I hope this helps you

SELECT
c.ContactID, c.Name, p.Phone, e.Address
FROM contacts c
LEFT OUTER JOIN email e ON c.ContactID = e.ContactID AND e.type = 'Office'
LEFT OUTER JOIN phone p ON c.ContactID = p.ContactID AND p.type = 'Office'


Eralper
http://www.kodyaz.com