CODEWITHSUNDEEP
mysqlsql
Yeol
Yeol

Reputation: 139

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!

Upvotes: 0

Views: 1657

Answers (2)

Tanuj Yadav
Tanuj Yadav

Reputation: 1299

try using INNER JOIN instead of left join.

Upvotes: 1

gr1zzly be4r
gr1zzly be4r

Reputation: 2152

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.

Upvotes: 1

Related Questions