SQLite Forum

Join Question
Login
I have a situation where I need to join 2 columns in one table to different rows in a second table.  I have tried to simplify the problem as follows:

I have a table of Names.  I also have a table of Support information.  This table includes a Main and Backup contact person for each row.  I would like to create a query that joins the Main and Backup keys from the Support table with the appropriate Name from the Names table.

This is as far as I get:

DROP Table if exists 'Names';
CREATE TABLE 'Names' (Key int, Name varchar(10));
INSERT INTO 'Names' (Key, Name) 
             Values  (10,  'Bob'),
                     (72,  'Sally'), 
                     (73,  'Jose'),
                     (106, 'Amanda');
                     
DROP Table if exists 'Support';
CREATE TABLE 'Support'(Key char, Main int, Backup int);
INSERT INTO 'Support' (Key, Main, Backup) 
             Values  ('A',  10, 72),
                     ('B', 106, 73);
                     
SELECT Support.MAIN   as MainKey,
       Names.Name     as MainName,
       Support.Backup as BackupKey
  from Support
  JOIN Names on (Support.Main = Names.Key)

I cannot figure out how to add the Names.Name for the Support.Backup reference to get
Desired Output
KEY	MainKey  Main_Name  BackupKey  Backup_Name
A	  10	    Bob	       72	 Sally
B	 106	    Amanda     73	 Jose