CODEWITHSUNDEEP
sqlsql-servercommon-table-expression
Newbie
Newbie

Reputation: 771

WITH clause in SQL Server 2014

I have a query 

With Price as
(
      select DISTINCT ID, A,B,C,D from AB_TABLE
      WHERE TYPE = 'FULL'
)

I need to able to join this with another query

With New_Price as
(
      select DISTINCT ID, A,B,C from AB_TABLE
      WHERE TYPE = 'HALF'
)

How could I achieve this? I need this query for creating a view, so cant use SELECT with the WITH clause. My apologies if this is confusing. But the query inside Price and New_Price is actually complicated and I want to join both of them to show records from both of them, probably by applying a UNION. Can someone please help here.

Upvotes: 0

Views: 404

Answers (3)

D-Shih
D-Shih

Reputation: 46219

SQL-Server VIEW can use multiple CTE.

You can follow this 

CREATE VIEW
AS
WITH
   CTE1 AS (SELECT * FROM T),
   CTE2 AS (SELECT * FROM T),
   CTE3 AS (SELECT * FROM T)
SELECT * CTE

Here is a simple

CREATE VIEW test 
AS 
  WITH price 
       AS (SELECT DISTINCT id, 
                           a, 
                           b, 
                           c, 
                           d 
           FROM   ab_table 
           WHERE  type = 'FULL'), 
       new_price 
       AS (SELECT DISTINCT id, 
                           a, 
                           b, 
                           c 
           FROM   ab_table 
           WHERE  type = 'HALF') 
  SELECT * 
  FROM   (SELECT *
          FROM   price 
          UNION 
          SELECT *
          FROM   new_price) t

Upvotes: 0

sandeep rawat
sandeep rawat

Reputation: 4957

I think you are looking for .

;With Price as (
      select DISTINCT ID, A,B,C,D
      from AB_TABLE
      WHERE TYPE = 'FULL'
    ),
    New_Price as (
     select DISTINCT ID, A,B,C
     from AB_TABLE
     WHERE TYPE = 'HALF'
   )

select ID, A,B,C from Price
 union ALL
select ID, A,B,C from New_Price

or 

select DISTINCT ID, A,B,C,D
      from AB_TABLE
      WHERE TYPE = 'FULL' or TYPE = 'HALF'

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269443

WITH handles multiple CTEs`:

With Price as (
      select DISTINCT ID, A,B,C,D
      from AB_TABLE
      WHERE TYPE = 'FULL'
    ),
    New_Price as (
     select DISTINCT ID, A,B,C
     from AB_TABLE
     WHERE TYPE = 'HALF'
   )
select . . .
from . . .

Upvotes: 2

Related Questions