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

Question

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

SELECT     T1 ID     T1 PhoneNumber1      T1 PhoneNumber2     T2A SomeOtherField AS  quot SomeOtherField of PhoneNumber1 quot       T2B SomeOtherField AS  quot SomeOtherField of PhoneNumber2 quot    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

User · Answer

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

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

Examples related to sql

Examples related to join