This is the third article in the series of articles on Common Table Expression in Sql Server. Below are the other articles in this series:
Introduction to Common Table Expression (a.k.a CTE)
Nested Common Table Expressions
Multiple CTEs in a Single Query
Many a times we come across a scenario, where we need to use multiple CTEs in a single query. For instance assume first CTE gives employee details and second CTE gives total salary paid to the employee year to date after performing all the aggregation of the salary paid from the beginning of the year to till date. So, here combining the first CTE with the Second CTE in a single query gives a meaning ful details of employee with his year to date salary.
Below is very basic example of using multiple CTEs in a single query:
WITH FirstCTE AS(SELECT 1 EmployeeId,'Shreeganesh Biradar' Name) , SecondCTE AS (SELECT 1 EmployeeId, '$ 100000' YTDSalary) SELECT FC.EmployeeId, FC.Name, SC.YTDSalary FROM FirstCTE FC INNER JOIN SecondCTE SC ON FC.EmployeeId = SC.EmployeeId