Home > DeveloperSection > Forums > How To Use Common Table Expressions
Ankit Singh

Total Post:341

Points:2389
Posted on    March-09-2016 9:12 PM

 Database SQL Server  SQL 
Ratings:


 1 Reply(s)
 385  View(s)
Rate this:
We want to To Use Common Table Expressions. How will do this please help me.


aditya kumar Patel

Total Post:250

Points:1782
Posted on    March-09-2016 9:17 PM

A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.

CREATE TABLE [CUSTOMER]

(

[CUST_ID] [bigint] IDENTITY(1,1)PRIMARY KEY,

[CUST_NAME] [varchar](200) NULL,

[CUST_EMAILID] [varchar](100) NULL,

[CUST_PHONE] [varchar](20) NULL,

[CUST_ADDRESS] [varchar](800) NULL

)

SELECT * FROM [CUSTOMER]

 

CREATE TABLE [ORDER]

(

[ORD_ID] [bigint] IDENTITY(1,1) PRIMARY KEY,

[CUST_ID] [bigint] NULL,

[PRODUCT_ID] [bigint] NULL,

[ORD_DATE] [date] NULL

)

SELECT * FROM [ORDER]

 

CREATE TABLE [PRODUCT]

(

[PRODUCT_ID] [bigint] IDENTITY(1,1) PRIMARY KEY,

[PRODUCT_NAME] [varchar](200) NULL,

[PRICE] [decimal](10, 2) NULL

)

SELECT * FROM [PRODUCT]

 

 

;With CTE(ORD_ID, ORD_DATE, CUST_NAME,CUST_EMAILID,CUST_PHONE,PRODUCT_NAME,PRICE)

AS

(

SELECT O.ORD_ID,O.ORD_DATE,C.CUST_NAME,C.CUST_EMAILID,C.CUST_PHONE,P.PRODUCT_NAME,P.PRICE FROM

[ORDER] AS [O] INNER JOIN CUSTOMER AS [C] ON O.CUST_ID=C.CUST_ID

INNER JOIN PRODUCT AS [P] ON P.PRODUCT_ID=O.PRODUCT_ID

)

SELECT * FROM CTE WHERE PRODUCT_NAME='COMPUTER' --Using CTE

 

 


Modified On Mar-10-2016 01:15:31 AM

Don't want to miss updates? Please click the below button!

Follow MindStick