What's the best way to join on the same table twice?

Question

First, I would try and refactor these tables to get away from using phone numbers as natural keys. I am not a fan of natural keys and this is a great example why. Natural keys, especially things like phone numbers, can change and frequently so. Updating your database when that change happens will be a HUGE, error-prone headache. *

Method 1 as you describe it is your best bet though. It looks a bit terse due to the naming scheme and the short aliases but... aliasing is your friend when it comes to joining the same table multiple times or using subqueries etc.

I would just clean things up a bit:

SELECT t.PhoneNumber1, t.PhoneNumber2, 
   t1.SomeOtherFieldForPhone1, t2.someOtherFieldForPhone2
FROM Table1 t
JOIN Table2 t1 ON t1.PhoneNumber = t.PhoneNumber1
JOIN Table2 t2 ON t2.PhoneNumber = t.PhoneNumber2

What i did:

• No need to specify INNER - it's implied by the fact that you don't specify LEFT or RIGHT
• Don't n-suffix your primary lookup table
• N-Suffix the table aliases that you will use multiple times to make it obvious

*One way DBAs avoid the headaches of updating natural keys is to not specify primary keys and foreign key constraints which further compounds the issues with poor db design. I've actually seen this more often than not.

The first is good unless either Phone1 or (more likely) phone2 can be null. In that case you want to use a Left join instead of an inner join.

It is usually a bad sign when you have a table with two phone number fields. Usually this means your database design is flawed.

You could use UNION to combine two joins:

SELECT Table1.PhoneNumber1 as PhoneNumber, Table2.SomeOtherField as OtherField
  FROM Table1
  JOIN Table2
    ON Table1.PhoneNumber1 = Table2.PhoneNumber
 UNION
SELECT Table1.PhoneNumber2 as PhoneNumber, Table2.SomeOtherField as OtherField
  FROM Table1
  JOIN Table2
    ON Table1.PhoneNumber2 = Table2.PhoneNumber

The first method is the proper approach and will do what you need. However, with the inner joins, you will only select rows from Table1 if both phone numbers exist in Table2. You may want to do a LEFT JOIN so that all rows from Table1 are selected. If the phone numbers don't match, then the SomeOtherFields would be null. If you want to make sure you have at least one matching phone number you could then do WHERE t2.PhoneNumber IS NOT NULL OR t3.PhoneNumber IS NOT NULL

The second method could have a problem: what happens if Table2 has both PhoneNumber1 and PhoneNumber2? Which row will be selected? Depending on your data, foreign keys, etc. this may or may not be a problem.

My problem was to display the record even if no or only one phone number exists (full address book). Therefore I used a LEFT JOIN which takes all records from the left, even if no corresponding exists on the right. For me this works in Microsoft Access SQL (they require the parenthesis!)

SELECT t.PhoneNumber1, t.PhoneNumber2, t.PhoneNumber3
   t1.SomeOtherFieldForPhone1, t2.someOtherFieldForPhone2, t3.someOtherFieldForPhone3
FROM 
(
 (
  Table1 AS t LEFT JOIN Table2 AS t3 ON t.PhoneNumber3 = t3.PhoneNumber
 )
 LEFT JOIN Table2 AS t2 ON t.PhoneNumber2 = t2.PhoneNumber
)
LEFT JOIN Table2 AS t1 ON t.PhoneNumber1 = t1.PhoneNumber;