SQLite Forum


1 forum post by user Jerry47

12:59 Post: Join Question (artifact: 8af9ef6a43 user: Jerry47)

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