I’ve been working on a query recently that has been doing my nut in. It involved five tables, possibly six depending how I wanted to do it. I had several tables that were going to be inner joined to each other. Then there were two tables which would be inner joined together, but outer joined to the other tables. I only wanted to return any rows from the first of those two tables if there were also matching rows in the second of the two tables.
So, I’m inner joining Category to SubCategory and SubCategory_School and School. That’s the easy part.
I’m only interested in rows from KeyIndicator where there are corresponding entries in School_KeyIndicator joining them to the School table. And that’s where I was having the difficulty.
I wasn’t getting anywhere with this, and couldn’t find anything on the web other than very simple examples joining two tables together. I had a look to see if Pinal Dave’s excellent SQL Authority blog had any useful information. He didn’t but he did link to an article by Coldfusion über-blogger Ben Nadel, Grouping JOIN Clauses In SQL, which was exactly what I was looking for.
SELECT C.ID AS CategoryID, C.Name AS CategoryName, SC.ID AS SubCategoryID, SC.Name AS SubCategoryName, KI.ID, KI.Name, S.ID AS SchoolKIID, S.Data, SC_S.ID AS ScoreID , SC_S.Score FROM Category C INNER JOIN SubCategory SC ON C.ID = SC.CategoryID LEFT OUTER JOIN ( KeyIndicator KI INNER JOIN School_KeyIndicator S ON KI.ID = S.KeyIndicatorID AND S.School = #URL.ID# ) ON SC.ID = KI.SubCategoryID LEFT OUTER JOIN SubCategory_School SC_S ON SC.ID = SC_S.SubCategoryID AND SC_S.School = #URL.ID# ORDER BY C.ID, SC.ID, KI.OrderBy, KI.ID
In the end I eliminated the School table from my query, as I didn’t actually need it. However what really made this work was the nested join, i.e. the part inside the brackets. Basically I do my INNER JOIN between KeyIndicator and School_KeyIndicator. Because that’s in the bracket, that gets evaluated first. Then the result of that is LEFT OUTER JOINed to the SubCategory table.