SQL Inception Query – a query within a query… BRRAAWW!

The SQL Inception Query (queue the “BRRRAAAAWWWW!”) is otherwise known as a Recursive CTE (Yawn.)

If you’re job is anything like mine, then you’ve probably had to create reports and stored procedures for databases you had no part in developing. Most of the time its no big deal. Certainly companies like Microsoft, with big developing budgets and programmers from MIT know how to design a database that doesnt require a PhD to manipulate, right?

Well, sometimes you are not so lucky. Enter this ProductStructure table, which I can only imagine was designed by an infant or someone who has completely given up on their career as a database administrator.

ParentPart ComponentPart QtyPerUnit
A101 B102 1
B102 X100 2
B102 X200 2
B102 BX300 2
A101 C103 1
D105 Z106 3

As you can see, there isnt a whole lot to it. The whole columns of data meant to tell me what components make up a part. It works well if I’m interested in finding out what components make up B102. But what about A101? It has B102 as a component and B102 is made up of 3 components. Oy vey!

Thankfully, the SQL gods have provided us with Recursive CTE’s, which is without a doubt my #1 favorite term to throw down at SQL meetups to sound like I’m the smartest guy there. (Take that “Guy who is always talking about CONCAT()”!)

Using recursive CTE’s we can run a multi tier query on this table to get all the components of a part, as well as all the components of those components, etc etc, so on a so forth. Hmmm, sort of reminds me of that Xzibit meme:

35738978

Anywho, check it out!

USE MyDatabase
GO
DECLARE @TheMainPart char(30) = 'A101';

WITH PartList (ParentPart, ComponentPart, QtyPerUnit) as
(
---define the anchor or main part in your query
SELECT e.ParentPart, e.ComponentPart, e.QtyPerUnit
FROM dbo.ProductStructure AS e
WHERE e.ParentPart = @TheMainPart
UNION ALL
---define recursive
SELECT e.ParentPart, e.ComponentPart, e.QtyPerUnit
FROM dbo.ProductStructure AS e
INNER JOIN PartList AS d
ON e.ParentPart = d.ComponentPart
)

SELECT *
FROM BOM;

Using that query, our return will exclude anything in the table that is not a component or a component of a component for Part A101.

ParentPart ComponentPart QtyPerUnit
A101 B102 1
B102 X100 2
B102 X200 2
B102 BX300 2
A101 C103 1

Not a big deal when my example includes a total of 6 parts, (we really just queried for everything except D105) but very helpful when your database consists of thousands parts.

To find out more about Recursive CTE’s you can check out this MSDN Article or check out this blog post, which give an example using the AdventureWorks database.

Advertisements

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