CODEWITHSUNDEEP
sqlsql-server
mHelpMe
mHelpMe

Reputation: 6668

Using Inner Join & Distinct in one query

I have two tables (shown below) called Merge_Codes & Factors. I am trying to write a query that takes the distinct countries from the factors table and joins this to the Merge_Codes table and returns the region.

Please see the results table at the bottom to see what I mean. I am trying to do this in one query but am getting no where near the solution, as you can see from the sql below.

SELECT * FROM Merge_Codes
INNER JOIN ON
(SELECT DISTINCT Country FROM Factors)

Merge_Codes Table

Region     Country 
EU         Germany
EU         France
EU         Italy
Asia       Japan
Asia       Hong Kong
NA         Canada
NA         USA
SA         Brazil
SA         Peru
SA         Chile

Factors Table

Factor     Country 
ABC        Germany      
ABC        Germany      
ABC        Japan        
ABC        USA          
ABC        USA          
ABC        Hong Kong

Result

Country    Region
Germany    EU
Japan      Asia
USA        NA
Hong Kong  Asia

Upvotes: 0

Views: 76

Answers (2)

Mitch Wheat
Mitch Wheat

Reputation: 300529

select
    MC.Country,
    MC.Region
from 
    (SELECT DISTINCT Country FROM Factors) DC
    INNER JOIN Merge_Codes MC ON MC.Country = DC.Country

Upvotes: 2

Dumitrescu Bogdan
Dumitrescu Bogdan

Reputation: 7267

select mc.*
from Merge_Codes mc
join (select distinct Country FROM Factors) f
  on mc.Country = f.Country

Upvotes: 3

Related Questions