Multi CTE is a Single SELECT Statement

Introduction

In this article we are going to demonstrate a simple example of CTE. How we can use multiple CTE is a single SELECT statement. Hope it will be informative and you will enjoy it.

How we use Multi CTE is SELECT

Step – 1 [ Create Base Table and Put Values in it ]

IF OBJECT_ID(N'[dbo].[tbl_Employee]', N'U')IS NOT NULL

   BEGIN

      DROP TABLE [dbo].[tbl_Employee];

   END

GO

CREATE TABLE [dbo].[tbl_Employee]

   (

     EmpID      INT         NOT NULL PRIMARY KEY,

     EmpName    VARCHAR(50) NOT NULL,

     EmpGrade   CHAR(1)     NOT NULL

   );

GO

IF OBJECT_ID(N'[dbo].[tbl_EmployeeSal]', N'U')IS NOT NULL

   BEGIN

      DROP TABLE [dbo].[tbl_EmployeeSal];

   END

GO

CREATE TABLE [dbo].[tbl_EmployeeSal]

   (

     EmpID      INT           NOT NULL PRIMARY KEY,

     Sal        NUMERIC(18,2) NOT NULL,

     Bonus      NUMERIC(18,2) NULL

   );

GO

ALTER TABLE [dbo].[tbl_EmployeeSal]

ADD CONSTRAINT  FK_EmpID FOREIGN KEY(EmpID) REFERENCES [dbo].[tbl_Employee](EmpID);

GO

INSERT INTO [dbo].[tbl_Employee]

       (EmpID, EmpName, EmpGrade)

VALUES (101, 'Joydeep Das', 'A'),

       (102, 'Deepasree Das', 'B'),

       (103, 'Soumen Bhoumik', 'C');

GO

INSERT INTO [dbo].[tbl_EmployeeSal]

       (EmpID, Sal, Bonus)

VALUES (101, 5000.00, 200.00),

       (102, 4000.00, 180.00),

       (103, 2500.00, 100.00);

GO

Step – 2 [ Using Multi CTE is a Single SELECT Statement ]

;WITH EmployeeDetails

     (EmpID, EmpName, EmpGrade)

AS

(

    SELECT EmpID, EmpName, EmpGrade

      FROM   [dbo].[tbl_Employee]

),

     EmployeeSalary

     (EmpID, Sal, Bonus)

AS

(

    SELECT EmpID, Sal, ISNULL(Bonus, 0) AS Bonus

      FROM   [dbo].[tbl_EmployeeSal]

)

SELECT  a.EmpID, a.EmpName, a.EmpGrade, b.Sal, b.Bonus,
        b.Sal + b.Bonus AS TotalSal

FROM    EmployeeDetails AS a

        INNER JOIN EmployeeSalary AS b ON a.EmpID = b.EmpID;

Output:

EmpID   EmpName                      EmpGrade          Sal                Bonus      TotalSal

101         Joydeep Das                    A                           5000.00        200.00      5200.00

102         Deepasree Das                 B                           4000.00        180.00      4180.00

103         Soumen Bhoumik            C                           2500.00        100.00      2600.00

Step – 3 [ Using a CTE Reference within other CTE is Single SELECT Statement ]

;WITH EmployeeDetails

     (EmpID, EmpName, EmpGrade)

AS

(

    SELECT EmpID, EmpName, EmpGrade

      FROM   [dbo].[tbl_Employee]

),

     EmployeeSalary

     (EmpID, EmpName, EmpGrade, Sal, Bonus, TotalSal)

AS

(

    SELECT b.EmpID, b.EmpName, b.EmpGrade, a.Sal, a.Bonus,

           a.Sal + a.Bonus AS TotalSal

    FROM   [dbo].[tbl_EmployeeSal] AS a

           INNER JOIN EmployeeDetails AS b ON a.EmpID = b.EmpID

)

SELECT  *

FROM    EmployeeSalary;

Output:

EmpID   EmpName                      EmpGrade          Sal                Bonus           TotalSal

101         Joydeep Das                    A                           5000.00        200.00          5200.00

102         Deepasree Das                 B                           4000.00        180.00         4180.00

103         Soumen Bhoumik            C                           2500.00        100.00         2600.00

Hope you like it.

Posted by: MR. JOYDEEP DAS