Join Tables without losing Data I am in a situation where I need to query a database for data, but am struggling on how to properly form the query.  Essentially I need to get data from the following tables:

Table: Contact
    contact_id INT [Primary Key]
    first_name VARCHAR(50)
    last_name  VARCHAR(50)

Table: Contact_Phone
    contact_id   INT [Foreign Key/Composite Primary Key]
    phone_number VARCHAR(15) [Composite Primary Key]
    extension    VARCHAR(10), NULL

Table: Contact_Fax
    contact_id   INT [Foreign Key/Composite Primary Key]
    fax_number   VARCHAR(15) [Composite Primary Key]

Table: Contact_Email
    contact_id   INT [Foreign Key/Composite Primary Key]
    email        VARCHAR(100) [Composite Primary Key]


Now not every single contact in my Contacts table has a Phone, Fax, or Email.  Some just have a phone or fax.  Others just an email.  There is one contact I know only has a phone number.

Now my issue is, if I try and make a simple query joining all of these tables look for specific contact information, if for any one of them there is no entry (for example, my one contact which doesn't have a fax or email) then I get no data being returned at all.

So the question is, how do I put this query together so that if I am trying to look up a specific contact's information, I will still be able to see all of the data (Phone/Fax/Email) even if one of them happens to be blank?
Use an outer join:

SELECT c.first_name, c.last_name, p.phone_number, p.extension, f.fax_number, e.email
FROM Contact c
LEFT OUTER JOIN Contact_Phone p ON c.ContactID = p.ContactID
LEFT OUTER JOIN Contact_Fax   f ON c.ContactID = f.ContactID
LEFT OUTER JOIN Contact_Email e ON c.ContactID = e.ContactID


Any missing information will show up as NULL.

This assumes that all contacts will at the very least have a master record in Contact.