"join" a subquery with (a subquery of) itself

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

"join" a subquery with (a subquery of) itself

Author

Topic

viktors
Starting Member

7 Posts

Posted - 2012-04-21 : 09:27:38

Hi!

I have a scenario with a calculation in a "cascade" of subqueries. I use this "cascade" design as calculated columns, referring to their aliases, are used to calculate other calculated columns in the upper levels of the cascade.

At any level of the "cascade" I can join the subquery (created in the previous level and having an alias) with, for example, a table like so:

SELECT * FROM (SELECT JoinColumn, .... FROM ...) SubAlias
INNER JOIN Table1 t ON t.JoinColumn = SubAlias.JoinColumn

I would like to "reuse" a subquery and do something like:

SELECT * FROM (SELECT JoinColumn, .... FROM ...) SubAlias
INNER JOIN SubAlias s ON s.JoinColumn = SubAlias.JoinColumn

but it results in 'invalid object name' error when executing this.

I know I can break up my select "cascade" put temporary results in a table variable, do "parallel" calculation and but them back together, but isn't there a way to do it in a single select?...

Thanks,
v.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-04-21 : 10:10:34

Sounds like you want to use a common table expression (CTE). See here for some more info: http://msdn.microsoft.com/en-us/library/ms190766.aspx

viktors
Starting Member

7 Posts

Posted - 2012-04-21 : 16:43:17

robvolk, CTE would be an option but I'm already using it for other purpose in my multi step calculation.

If have a calculation with 5 steps and in step 3 want to use the results of step 2 in combination with some aggregates from the results of step 2 is the only option available to put the results of step 2 in some temporary table or table variable, and resume the calculation by combining the temporary table with an aggregating subquery that uses the temporary table? (if CTE isn't available)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-21 : 17:09:07

yep...you've either use CTE or temporary table for this scenario. You can even use multiple CTE is nature of logic is different for each step

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-04-21 : 19:10:37

To elaborate on visakh's explanation:

WITH CTE1(a,b,c) AS (SELECT a,b,c from Table1),
CTE2 (x,y,z) AS (SELECT x,y,z FROM Table2),
CTE2Agg (X, AvgY, AvgZ, MaxY, MaxZ) AS (SELECT x, Avg(y), Avg(z), Max(y), Max(z) FROM CTE2 GROUP BY X)
CTE3 AS (SELECT * FROM CTE2 INNER JOIN CTE2Agg ON CTE2.X=CTE2Agg.X)
... keep stacking CTEs as deep as you need, then do the final SELECT from them

viktors
Starting Member

7 Posts

Posted - 2012-04-22 : 04:57:05

Thank you very much visakh16 and robvolk!
The solution with multiple/nested CTE's worked great for me (especially as I used it in combination with derived tables).
I had missed the the concept of multiple CTEs / CTE stack as the examples of CTEs I had seen involved just a single CTE.

In the beginning I tried to do the same with the stack of derived tables only but in msdn now I see that:
Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-22 : 13:14:35

yep..thats why we usually associate CTE with recursive solutions.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources