SQLite Forum

Join Question
Login

Join Question

(1) By Jerry47 on 2020-09-04 12:59:42 [link] [source]

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

(2) By TripeHound on 2020-09-04 14:47:18 in reply to 1 [link] [source]

One method, (there may be others) is to use sub-selects:

select
    Support.main as MainKey,
    ( select Name from Names where Names.Key = Support.Main  ) as MainName, 
    Support.Backup as BackKey,
    ( select Name from Names where Names.Key = Support.Backup) as BackName
from Support ;

MainKey     MainName    BackKey     BackName
----------  ----------  ----------  ----------
10          Bob         72          Sally
106         Amanda      73          Jose

For symmetry, I've used sub-select for both names; you could leave the JOIN in place for one of the names and use a sub-select for the other.

(3.2) By AAsk (aa2e72e) on 2020-09-04 15:44:11 edited from 3.1 in reply to 1 [source]

Deleted

(4) By AAsk (aa2e72e) on 2020-09-04 16:07:23 in reply to 3.2 [link] [source]

Another way

.mode column
.headers on

SELECT a.Main,
	c.Name AS Name,
	a.BACKUP,
	a.Name AS OtherName
FROM (
	SELECT *
	FROM SUPPORT a
	INNER JOIN NAMES b ON a.BACKUP = b.KEY
	) a
INNER JOIN NAMES c ON a.Main = c.KEY;

Main  Name    Backup  OtherName
----  ------  ------  ---------
10    Bob     72      Sally
106   Amanda  73      Jose

(5) By Richard Damon (RichardDamon) on 2020-09-04 16:11:09 in reply to 1 [link] [source]

My first thought (untested)

SELECT Support.Key as Support_Key, Main.name as Main_Name, Back.name as Back_Name FROM Support JOIN Names as Main on Support.Main = Main.Key JOIN Names as Back on Support.Backup = Back.Key

Since you are pulling from Names twice, you need to give it aliases to refer to the two copies.

(6.1) By John Dennis (jdennis) on 2020-09-05 07:57:01 edited from 6.0 in reply to 1 [link] [source]

select s.main,a.name,s.backup,b.name
from support s, names a, names b
where s.main=a.key
and s.backup=b.key;

Main  Name    Backup  Name
----  ------  ------  -----
10    Bob     72      Sally
106   Amanda  73      Jose

Edit: This is essentially the same solution at the previous post. Apologies.