Simple SELECT Query is resulting in Cartesian Join?

Question

I have a somewhat simple query where I am returning 4 fields: [Year], [ServiceCredited], [Salary], and [CoveredEmployer].

Using Table1, I can retrieve first 3 fields, but I cannot get the [CoveredEmployer]. To get the [CoveredEmployer], I have to join to Table2 which has more details like the Members Employer_Org_ID, and then join Table2 to Table3 using the Employer_Org_Id to get the Employer Name, or [CoveredEmployer].

SELECT a.SCHOOL_YEAR as Year, a.TOTAL_SERVICE_CREDIT as ServiceCredited, a.RETIREMENT_SALARY as Salary, c.ORG_NAME as CoveredEmployer 
FROM TBL1 a 
INNER JOIN TBL2 b ON a.MEMBER_ACCOUNT_ID = b.MEMBER_ACCOUNT_ID
INNER JOIN TBL3  c ON b.ORG_ID = c.ORG_ID 
WHERE a.MEMBER_ACCOUNT_ID = 136079
ORDER BY YEAR DESC;

I am then, in code, grouping the results by [Year] and where there is more than 1 Employer, having the [CoveredEmployer] value be "Multiple Employers":

var lyosGroupedByYear = lyos.GroupBy(x => x.Year).Select(x => new Years_Of_Service()
            {
                Year = x.Key,
                //ServiceCredited = x.Sum(y => y.ServiceCredited),
                //Salary = x.Sum(y => y.Salary),
                ServiceCredited = x.First().ServiceCredited,
                Salary = x.First().Salary,
                CoveredEmployer = x.Count() > 1 ? "Multiple Employers" : x.First().CoveredEmployer
            }).ToList();

The problem with my above query however is that I am getting a Cartesion Join, thus resulting in "Multiple Employers" on output for each record that I get grouped (below is simply the 2013 & 2014 results, the actual results go back to 1982 with same issue):

2014            Special School Dist-St. Louis
2014            Special School Dist-St. Louis
2014            Special School Dist-St. Louis
2014            Special School Dist-St. Louis
2014            Special School Dist-St. Louis
2014            Special School Dist-St. Louis
2014            Special School Dist-St. Louis
2014            Special School Dist-St. Louis
2014            Special School Dist-St. Louis
2014            Special School Dist-St. Louis
2014            Special School Dist-St. Louis
2014            Special School Dist-St. Louis
2014            Special School Dist-St. Louis
2014            Special School Dist-St. Louis
2014            Special School Dist-St. Louis
2014            Special School Dist-St. Louis
2014            Special School Dist-St. Louis
2014            Special School Dist-St. Louis
2014            Special School Dist-St. Louis
2014            Special School Dist-St. Louis
2014            Special School Dist-St. Louis
2014            Special School Dist-St. Louis
2014            Special School Dist-St. Louis
2014            Hazelwood R-I Schools
2014            St. Louis Community College
2014            Special School Dist-St. Louis
2014            St. Louis Community College
2014            Special School Dist-St. Louis
2014            St. Louis Community College
2014            Special School Dist-St. Louis
2014            St. Louis Community College
2014            Special School Dist-St. Louis
2014            St. Louis Community College
2014            Special School Dist-St. Louis
2014            St. Louis Community College
2014            Special School Dist-St. Louis
2014            St. Louis Community College
2014            St. Louis Community College
2013    1   95476.8 Special School Dist-St. Louis
2013    1   95476.8 Special School Dist-St. Louis
2013    1   95476.8 Special School Dist-St. Louis
2013    1   95476.8 Special School Dist-St. Louis
2013    1   95476.8 Special School Dist-St. Louis
2013    1   95476.8 Hazelwood R-I Schools
2013    1   95476.8 St. Louis Community College
2013    1   95476.8 Special School Dist-St. Louis
2013    1   95476.8 St. Louis Community College
2013    1   95476.8 Special School Dist-St. Louis
2013    1   95476.8 St. Louis Community College
2013    1   95476.8 Special School Dist-St. Louis
2013    1   95476.8 St. Louis Community College
2013    1   95476.8 Special School Dist-St. Louis
2013    1   95476.8 St. Louis Community College
2013    1   95476.8 Special School Dist-St. Louis
2013    1   95476.8 St. Louis Community College
2013    1   95476.8 Special School Dist-St. Louis
2013    1   95476.8 St. Louis Community College
2013    1   95476.8 Special School Dist-St. Louis
2013    1   95476.8 Special School Dist-St. Louis
2013    1   95476.8 St. Louis Community College
2013    1   95476.8 Special School Dist-St. Louis
2013    1   95476.8 Special School Dist-St. Louis
2013    1   95476.8 Special School Dist-St. Louis
2013    1   95476.8 Special School Dist-St. Louis
2013    1   95476.8 Special School Dist-St. Louis
2013    1   95476.8 Special School Dist-St. Louis
2013    1   95476.8 Special School Dist-St. Louis
2013    1   95476.8 Special School Dist-St. Louis
2013    1   95476.8 Special School Dist-St. Louis
2013    1   95476.8 Special School Dist-St. Louis
2013    1   95476.8 Special School Dist-St. Louis
2013    1   95476.8 Special School Dist-St. Louis
2013    1   95476.8 Special School Dist-St. Louis
2013    1   95476.8 Special School Dist-St. Louis
2013    1   95476.8 Special School Dist-St. Louis
2013    1   95476.8 Special School Dist-St. Louis

This is my first time dealing with Cartesian Joins. Does anyone know a possible way to fix this issue in my query?

So you have multiple `tbl2` records for each `tbl1` record, and/or multiple `tbl3` records for each `tbl2` record? An sample of the raw data for one of the final results might be helpful. Why aren't you grouping/counting in your query though? — Alex Poole, Oct 09 '14 at 16:01
Thanks for the reply Alex. I ended up figuring out the issue. I had the `JOIN` correct for Tbl2 & Tbl3, but for Tbl1 & Tbl2 I was using the wrong Key value for the Join. I made a few other modifications, that was the source of my problem. — Analytic Lunatic, Oct 09 '14 at 16:30
@AnalyticLunatic Congratulations for having fixed your issue yourself. Could you please post -- and self-accept -- your solution so this question might appears as "answered". This is important for future visitors. — Sylvain Leroux, Oct 09 '14 at 17:52
Sure thing. Just curious, why does it take 2 days to self-accept an answer? Seems like most people will go off and move on to other things without remembering to accept their own answer after the time has passed. — Analytic Lunatic, Oct 09 '14 at 21:10

score 0 · Answer 1 · answered Oct 09 '14 at 21:13

Turns out I was joining Tbl1 & Tbl2 on the wrong field. Should have been MEMBER_CONTR_ID instead of MEMBER_ACCOUNT_ID:

SELECT a.SCHOOL_YEAR as Year, a.TOTAL_SERVICE_CREDIT as ServiceCredited, a.RETIREMENT_SALARY as Salary, c.ORG_NAME as CoveredEmployer 
FROM TBL1 a 
INNER JOIN TBL2 b ON a.MEMBER_CONTR_ID = b.MEMBER_CONTR_ID
INNER JOIN TBL3  c ON b.ORG_ID = c.ORG_ID 
WHERE a.MEMBER_ACCOUNT_ID = 136079
ORDER BY YEAR DESC;

Simple SELECT Query is resulting in Cartesian Join?

1 Answers1