SQL nested joins

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.

To try and explain it, here’s a rough ER diagram I put together in Gliffy.com:

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.