Joining SQL lookup / reference table with data tables

Question

Hello Community,

I have the following lookup table:

Key, CB_URL_KEY
001066b4-e17a-e811-a95c-00224800c9ff https://www.crunchbase.com/organization/cams-consulting
001366b4-e17a-e811-a95c-00224800c9ff https://www.crunchbase.com/organization/spenglerfox
f41266b4-e17a-e811-a95c-00224800c9ff https://www.crunchbase.com/organization/spenglerfox
6620e226-ac63-ea11-a811-000d3a86d68d https://www.crunchbase.com/organization/360-dotnet
ca1966b4-e17a-e811-a95c-00224800c9ff https://www.crunchbase.com/organization/spenglerfox

I have the following Data Tables

account table

Id, name, websiteurl
0886ef5e-bfca-eb11-bacc-0022481a7fa3 18 week support https://www.18weeksupport.com/
85b8b5d9-e07a-e811-a95c-00224800c3e8 19 Entertainment http://www.19entertainment.com
4762daf5-4933-ec11-b6e6-002248009c9d 1link http://www.1link.net.pk/
9200ee10-55a3-eb11-b1ac-000d3a86db41 20-20 Trustees http://2020trustees.co.uk/
5872314c-87bc-ea11-a812-000d3a86b410 21grams http://www.21grams.se
892594ae-02b5-e811-a96e-00224800c3e8 247TailorSteel https://www.247tailorsteel.com/
142da10f-71a3-ec11-983f-0022481b9a5f 2Connect http://www.2-connect.info/en/contact/netherlands/
6620e226-ac63-ea11-a811-000d3a86d68d 360 Dotnet https://360dotnet.co.uk/
57d35901-34d9-e911-a813-000d3a86d68d 360kompany AG https://www.kompany.com/
25163f80-198e-ea11-a811-000d3a86d68d 3D Plus http://www.3d-plus.com

organization table

name, legal_name, cb_url
19 Entertainment https://www.crunchbase.com/organization/19-entertainment
21GRAMS https://www.crunchbase.com/organization/21grams-llc
360 Dotnet 360 Dotnet https://www.crunchbase.com/organization/360-dotnet
21GRAMS https://www.crunchbase.com/organization/21grams-3973
2Connect https://www.crunchbase.com/organization/2connect-fa86
21GRAMS https://www.crunchbase.com/organization/21grams-2c2f
21GRAMS https://www.crunchbase.com/organization/21grams
2connect https://www.crunchbase.com/organization/2connect
ZZ Creative https://www.crunchbase.com/organization/zz-creative

I would like a table that combines all three tables that will join on the 'key' field in the lookup table with 'Id' field in Account table to produce the following output:

Id accountname cb_url orgName orgLegal_Name
6620e226-ac63-ea11-a811-000d3a86d68d 360 Dotnet https://www.crunchbase.com/organization/360-dotnet 360 Dotnet 360 Dotnet

Sample Data is a follows:

Lookup Table

CREATE TABLE CRM2CBURL_Lookup (  
    Key varchar(50),  
    CB_URL_KEY varchar(150))  
  
INSERT CRM2CBURL_Lookup VALUES  
('001066b4-e17a-e811-a95c-00224800c9ff','https://www.crunchbase.com/organization/cams-consulting'),  
('001366b4-e17a-e811-a95c-00224800c9ff','https://www.crunchbase.com/organization/spenglerfox'),  
('f41266b4-e17a-e811-a95c-00224800c9ff','https://www.crunchbase.com/organization/spenglerfox'),  
('6620e226-ac63-ea11-a811-000d3a86d68d','https://www.crunchbase.com/organization/360-dotnet'),  
('ca1966b4-e17a-e811-a95c-00224800c9ff','https://www.crunchbase.com/organization/spenglerfox')  
  
SELECT * FROM CRM2CBURL_Lookup  

The account table

CREATE TABLE account (  
    Id varchar(50),  
    name varchar(100),  
    websiteurl varchar(400))  
  
INSERT account VALUES  
('0886ef5e-bfca-eb11-bacc-0022481a7fa3','18 week support','https://www.18weeksupport.com/'),  
('85b8b5d9-e07a-e811-a95c-00224800c3e8','19 Entertainment','http://www.19entertainment.com'),  
('4762daf5-4933-ec11-b6e6-002248009c9d','1link','http://www.1link.net.pk/'),  
('9200ee10-55a3-eb11-b1ac-000d3a86db41','20-20 Trustees','http://2020trustees.co.uk/'),  
('5872314c-87bc-ea11-a812-000d3a86b410','21grams','http://www.21grams.se'),  
('892594ae-02b5-e811-a96e-00224800c3e8','247TailorSteel','https://www.247tailorsteel.com/'),  
('142da10f-71a3-ec11-983f-0022481b9a5f','2Connect','http://www.2-connect.info/en/contact/netherlands/'),  
('6620e226-ac63-ea11-a811-000d3a86d68d','360 Dotnet','https://360dotnet.co.uk/'),  
('57d35901-34d9-e911-a813-000d3a86d68d','360kompany AG','https://www.kompany.com/'),  
('25163f80-198e-ea11-a811-000d3a86d68d','3D Plus','http://www.3d-plus.com')  
  
SELECT * FROM account  

organizations table

CREATE TABLE organizations (  
    name nvarchar(100),  
    legal_name nvarchar(100),  
    cb_url nvarchar(150))  
  
INSERT organizations VALUES  
(N'19 Entertainment',N'',N'https://www.crunchbase.com/organization/19-entertainment'),  
(N'21GRAMS',N'',N'https://www.crunchbase.com/organization/21grams-llc'),  
(N'360 Dotnet',N'360 Dotnet',N'https://www.crunchbase.com/organization/360-dotnet'),  
(N'21GRAMS',N'',N'https://www.crunchbase.com/organization/21grams-3973'),  
(N'2Connect',N'',N'https://www.crunchbase.com/organization/2connect-fa86'),  
(N'21GRAMS',N'',N'https://www.crunchbase.com/organization/21grams-2c2f'),  
(N'21GRAMS',N'',N'https://www.crunchbase.com/organization/21grams'),  
(N'2connect',N'',N'https://www.crunchbase.com/organization/2connect'),  
(N'ZZ Creative',N'',N'https://www.crunchbase.com/organization/zz-creative')  
  
SELECT * FROM organizations  

Any help will be appreciated.

Answer 1

SELECT A.ID,  
 A.name AS accountname,  
 O.cb_url,  
 O.name AS orgName,  
 O.legal_name AS orgLegal_Name  
FROM account A  
INNER JOIN  
 CRM2CBURL_Lookup L  
ON A.ID = L.[Key]  
INNER JOIN   
 organizations O  
ON L.[CB_URL_KEY] = O.cb_url