0

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?

5

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.

4
  • 1
    If, for example, a contact has multiple phone numbers, the name will appear multiple times. – Jonathan Fite Aug 2 '18 at 19:49
  • @JonathanFite That's true. Worse, if a contact has 2 phone numbers and 2 emails, you'll end up with 4 rows (showing each possible combination of phone and email). So techniques may vary based on how this result will be used/displayed. – BradC Aug 2 '18 at 20:17
  • true as well. I just thought it worth mentioning given the OP's apparent level of skill. – Jonathan Fite Aug 2 '18 at 20:19
  • If you don't want multiple entries for people with multiple phone numbers (etc.), you can use group_concat. – Charles Aug 2 '18 at 21:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.