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.
Another way: sqlite> .mode column sqlite> .headers on sqlite> SELECT a.KEY, ...> a.MAIN, ...> a.BACKUP, ...> b.NAME ...> FROM SUPPORT a ...> INNER JOIN NAMES b ON a.BACKUP = b.KEY; Key Main Backup Name --- ---- ------ ----- A 10 72 Sally B 106 73 Jose sqlite>
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.
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
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.