Yeol Yeol - 12 days ago 5
SQL Question

Join causes Cartesian Product

I preformed a left join on two tables, however cartesian products were produced while it doesnt on others. The sample result of the query can be seen in the pic below.
enter image description here

The query current query is:

SELECT
R.Region,
C.CountryName,
D.Year,
I.Income,
D.Completion_Rate AS 'Completion Rate',
D.Pupil_Teacher_Ratio AS 'Pupil-Teacher Ratio'
FROM
(SELECT
C.CountryCodeC AS 'CountryCode',
C.YearCC AS 'Year',
C.Completion_Rate,
R.Pupil_Teacher_Ratio
FROM
(SELECT
CountryCode AS 'CountryCodeC',
Data AS 'Completion_Rate',
YearC AS 'YearCC'
FROM
DataByYear
WHERE
SeriesCode = "SE.SEC.CMPT.LO.ZS"
AND YearC >= "2011%") C
LEFT JOIN
(SELECT
CountryCode AS 'CountryCodeR',
Data AS 'Pupil_Teacher_Ratio',
YearC AS 'YearCR'
FROM
DataByYear
WHERE
SeriesCode = "SE.SEC.ENRL.LO.TC.ZS"
AND YearC >= "2011%") R
ON C.CountryCodeC = R.CountryCodeR) D,
CountryRegion R,
Country C,
CountryIncome I
WHERE
R.CountryCode = D.CountryCode
AND R.CountryCode = C.CountryCode
AND I.CountryCode = D.CountryCode


What is the problem and how can I fix it. Thank you!

Answer

I think that you are also looking to join on Year for all of your queries. If you don't specify a year then each year will get joined with all other years and inflate your values. So you should add a condition like:

A.Year = B.Year

throughout your query.

Comments