CTE and inner join

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » CTE and inner join

12 posts, Page 2 of 2

««1 2

CTE and inner join

Rate Topic

Display Mode

Topic Options

Author

Message

peleg

Posted Tuesday, January 11, 2011 12:33 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 12:15 AM
Points: 123, Visits: 502

Yep. A CTE can be referenced multiple times inside of the statement immediately following it, but it can't be referenced by multiple statements within the query.

you mean it can be used only once inside an inner join and that's all the problem?

Post #1046086

Grant Fritchey

Posted Tuesday, January 11, 2011 12:38 PM

SSChampion

Group: General Forum Members
Last Login: Today @ 4:17 AM
Points: 13,377, Visits: 25,161

No. Not really. This is psuedo-code to describe the situation:

WITH MyCTE(...)

INSERT INTO MyTable
SELECT * FROM MyCTE AS x
JOIN MyCTE as y
ON x.ID = Y.ID
and x.Type=1
JOIN MyCTE AS z
ON x.ID = z.ID
AND z.Type=2

Something like this will work fine. But if I did this:

WITH MyCTE(...)

INSERT INTO MyTable
SELECT * FROM MyCTE as X

INSERT INTO MyOtherTable
SELECT * FROM MyCTE aS y

That will fail because the CTE only lives as long as the first statement following it's definition, regardless of how many times it's referenced within the statement.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software

Post #1046090

« Prev Topic | Next Topic »

12 posts, Page 2 of 2

««1 2

Permissions