CODEWITHSUNDEEP
mysql
TJ909
TJ909

Reputation: 11

MYSQL: Joining two different fields to same field in third table

I'm trying to join two different fields to the same field in a third lookup table. My data is structured like this:

Investments table:
Investment_ID
Company_Country_Code (e.g., US, UK, AU, FR)
Fund_Country_Code (e.g., US, UK, AU, FR)

Country Lookup Table:
Country_Code (e.g., US, UK, AU, FR)
Country_Name (e.g., United States, United Kingdom, Australia, France)

I would like to join both Company_Country_Code and Fund_Country_Code to the Country_Code table in the country lookup table and pull the respective Country_Names. I would like the data output like this:

(Investment_ID, Company_Country_Name, Fund_Country_Name)
1, United States, France
2, United Kingdom, Australia
3, France, United States

Is there a special kind of join to accomplish this?

Thanks!

Upvotes: 0

Views: 46

Answers (2)

John Ruddell
John Ruddell

Reputation: 25862

It all depends on what you want... if you want to filter out any results that are not in both tables then use an INNER JOIN.. if you want to join without filtering use a LEFT JOIN

SELECT i.Investment_ID, i.Company_Country_Name, i1.Fund_Country_Name
FROM country_lookup cl
JOIN investments i ON i.Company_Country_Code = cl.Country_Code
JOIN investments i1 ON i1.Fund_Country_Code = cl.Country_Code

without filtering the data you can do it with left joins like so

SELECT i.Investment_ID, i.Company_Country_Name, i1.Fund_Country_Name
FROM country_lookup cl
LEFT JOIN investments i ON i.Company_Country_Code = cl.Country_Code
LEFT JOIN investments i1 ON i1.Fund_Country_Code = cl.Country_Code

Upvotes: 1

Shadow Radiance
Shadow Radiance

Reputation: 1369

Nothing special - just make two joins:

SELECT 
  i.Investment_ID, 
  c.Country_Name as Company_Country_Name, 
  f.Country_Name as Fund_Country_Name 
FROM 
  Investments i
JOIN
  Country c ON i.Company_Country_Code = c.Country_Code
JOIN
  Country f ON i.Fund_Country_Code = f.Country_Code

Upvotes: 1

Related Questions