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

This is a little complicated, but I have 2 tables. Let's say the structure is something like this:

*Table1*
ID
PhoneNumber1
PhoneNumber2

*Table2*
PhoneNumber
SomeOtherField

The tables can be joined based on Table1.PhoneNumber1 -> Table2.PhoneNumber, or Table1.PhoneNumber2 -> Table2.PhoneNumber.

Now, I want to get a resultset that contains PhoneNumber1, SomeOtherField that corresponds to PhoneNumber1, PhoneNumber2, and SomeOtherField that corresponds to PhoneNumber2.

I thought of 2 ways to do this - either by joining on the table twice, or by joining once with an OR in the ON clause.

Method 1:

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

This seems to work.

Method 2:

To somehow have a query that looks a bit like this -

SELECT ...
FROM Table1
INNER JOIN Table2 
   ON Table1.PhoneNumber1 = Table2.PhoneNumber OR
      Table1.PhoneNumber2 = Table2.PhoneNumber

I haven't gotten this to work yet and I'm not sure if there's a way to do it.

What's the best way to accomplish this? Neither way seems simple or intuitive... Is there a more straightforward way to do this? How is this requirement generally implemented?

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.

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.

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

SELECT
    T1.ID
    T1.PhoneNumber1,
    T1.PhoneNumber2
    T2A.SomeOtherField AS "SomeOtherField of PhoneNumber1",
    T2B.SomeOtherField AS "SomeOtherField of PhoneNumber2"  
FROM
    Table1 T1
    LEFT JOIN Table2 T2A ON T1.PhoneNumber1 = T2A.PhoneNumber
    LEFT JOIN Table2 T2B ON T1.PhoneNumber2 = T2B.PhoneNumber
WHERE
    T1.ID = 'FOO';

LEFT JOIN or JOIN also return same result. Tested success with PostgreSQL 13.1.1 .

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;