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?
Best Answer
Use an outer join:
Any missing information will show up as
NULL
.This assumes that all contacts will at the very least have a master record in
Contact
.