SQL SELECT Columns From Multiple Tables Without Repeating Data

Question

I am trying to query 2 tables using the following sql statement in an attempt to return all records from each table that contains a specific id.

SELECT Phone.Phone, Email.Email FROM Contacts.Phone, Contacts.Email 
WHERE Phone.ContactId = :contactId AND Email.ContactId = :contactId

Contacts.Phone table contains 2 phone numbers for the given id and Contacts.Email contains 1 email for the given id. Using the above sql query, I get the following rows returned. This is just an example, of course, of a situation in where my result set from each table does match in number of rows.

Row 1: 555-555-5555 - [email protected]
Row 2: 666-666-6666 - [email protected]

The email is repeated in order to fill in the second row when I am trying to get:

Row 1: 555-555-5555 - [email protected]
Row 2: 666-666-6666 - NULL

I think I need to use a UNION to somehow join the tables, but I can't figure out exactly how to write the sql statement. Another option would be to perform 2 separate SQL queries, which would be easier but I figure performance wise it would be better to collect all data I need in one query.

I am using MySQL.

Answer 1

Use a left outer join to solve your problem, the query would look sonething like this if you were using MS SQL:

SELECT 
    Phone.Phone, 
    Email.Email 
FROM 
    Contacts.Phone 

Left Outer Join Contacts.Email ON Phone.ContactId = Email.ContactId

Your results will look like the following:

Row 1: 555-555-5555 - [email protected] 
Row 2: 666-666-6666 - NULL