Duncan's blog

February 5, 2009

SQL nested joins

Filed under: Uncategorized — duncan @ 12:00 am
Tags: , , ,

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:

6-way join

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.

About these ads

5 Comments »

  1. Genius! I was having the same exact problem, and this works flawlessly.

    Thanks!

    Comment by Martin — August 2, 2010 @ 6:12 pm | Reply

  2. This post just solved ALL of my problems. Thanks a TON!!

    Comment by Craig — May 25, 2011 @ 6:38 pm | Reply

  3. nice article….got new info about nested queries… :)

    Comment by deepak — August 11, 2011 @ 6:58 am | Reply

  4. Awesome! I was JUST having almost an identical problem!

    Comment by Kevin — September 15, 2011 @ 2:03 pm | Reply

  5. Thank you so much for this! I had two inner joined tables in a CTE but this works so much better.

    Comment by Ivan — May 28, 2012 @ 8:24 am | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 522 other followers

%d bloggers like this: