Duplicate records using JOIN for multiple tables

Question

Hello MS Community,

I need help as I just started learning SQL server. I have this issue in appending the "required" columns from 3 different tables using SQL server.

Following is the scenario:

I need the columns Male (Table2) and Female (Table 3) join the main Table 1. 

source files: life expectancy 

Table1: Country, Year, Total

Table2: Country, Year, Male

Table3: Country, Year, Female

I tried various ways of joining the columns from the 3 tables but to no avail. The last I tried this syntax but still I am not getting the right answer.

SELECT * FROM LifeExpectancyTotal t
INNER JOIN
(SELECT country, MIN (Male) AS M FROM LifeExpectancyMale
GROUP BY country) AS FinalMale
ON t.country = FinalMale.country
INNER JOIN
(SELECT country, MIN (Female) AS F FROM LifeExpectancyFemale
GROUP BY country) AS FinalFemale
ON t.country = FinalFemale.country

----------------------------------------------------------------------------------------------

Due to time constraint, I have used INDEX MATCH in excel to solve this issue. However, I like to know can this be sorted in SQL server. If can, what is the solution?59932-lifeexpectancy-male.txt

Thanks59866-lifeexpectancy-final.txt

59894-lifeexpectancy-total.txt

Answer 1

Select t.Country, t.Year, t.Total, m.Male, f.Female
From LifeExpectancyTotal t
Inner Join LifeExpectancyMale m On t.Country = m.Country And t.Year = m.Year
Inner Join LifeExpectancyFemale f On t.Country = f.Country And t.Year = f.Year
Order By t.Country, t.Year;

Tom

Answer 2

Thank you so much, Tom. :)

I didn't know that if there are more than 2 common columns I need to specify both the columns for this case it is Country and Year are the ones. I just indicate Country only and I ended up in multiple records.

Cheers Zarena