Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest

Using multiple CTEs in one query

This is a fairly simple thing to do, but I had to look it up the other day and thought it would make a good blog. This is a good example of a blog that everyone should write to show they know a particular skill.

TL;DR: Use a comma between CTE definitions.

Let’s imagine that you need to combine a few queries together and the code is complex. You decide to use a CTE to make things easier. Suppose I have this query:

SELECT p.title , p.firstname , p.lastname FROM Person.BusinessEntity be INNER JOIN Person.Person p ON p.BusinessEntityID = be.BusinessEntityID WHERE ( SELECT COUNT(*) FROM Person.BusinessEntityAddress bea WHERE bea.BusinessEntityID = be.BusinessEntityID ) > 1 AND ( SELECT COUNT(*) FROM Person.PersonPhone pp WHERE pp.BusinessEntityID = be.BusinessEntityID ) > 1

That’s ugly, and I want to move the subqueries. I know this is slightly contrived, but imagine that what you really want is a couple views like:

CREATE VIEW BEACount AS SELECT bea.BusinessEntityID , cnt = COUNT(*) FROM Person.BusinessEntityAddress bea WHERE bea.BusinessEntityID = be.BusinessEntityID CREATE VIEW PersonPhoneCount AS SELECT bea.BusinessEntityID , cnt = COUNT(*) FROM Person.PersonPhone pp WHERE pp.BusinessEntityID = be.BusinessEntityID

However you don’t want to create views, what you really want is to make both of these queries CTEs and then call them from the original query.

I wasn’t sure how to do this, so I Googled. The first result was my friend, Pinal Dave’s blog. It took me about 10 seconds to read his explanation and apply it to my issue.

WITH BEACountCTE AS ( SELECT bea.BusinessEntityID , cnt = COUNT(*) FROM Person.BusinessEntityAddress bea GROUP BY bea.BusinessEntityID ) , PersonPhoneCountCTE AS ( SELECT pp.BusinessEntityID , cnt = COUNT(*) FROM Person.PersonPhone pp GROUP BY pp.BusinessEntityID ) SELECT p.title , p.firstname , p.lastname FROM Person.BusinessEntity be INNER JOIN Person.Person p ON p.BusinessEntityID = be.BusinessEntityID INNER JOIN PersonPhoneCountCTE pp ON pp.BusinessEntityID = p.BusinessEntityID INNER JOIN BEACountCTE bea ON bea.BusinessEntityID = p.BusinessEntityID WHERE BEA.cnt > 1 AND PP.cnt > 1

Note that at the top I have one WITH statement and both of my CTEs are separated by commas.

Easy enough, a quick thing to look up, and a handy item to know. If you are breaking up queries and using CTEs to make things easier to read, use a comma between your CTEs.


Filed under: Blog Tagged: syndicated, T-SQL

Comments

Leave a comment on the original post [voiceofthedba.wordpress.com, opens in a new window]

Loading comments...