Joining Multiple Common Table Expressions

joining multiple common table expressions

Try this query, perhaps this is what you are looking for.

;WITH cte AS
 (SELECT dbo.Cable.*, 
         row_number() over(partition by dbo.Cable.TagNo order by dbo.Cable.CableRevision desc) as rn
  FROM dbo.Cable 
  WHERE dbo.Cable.CableRevision = @CoreRevision
  ), cte2 AS
 (SELECT dbo.Cable.TagNo, dbo.Core.*, 
         row_number() over(partition by dbo.Core.CoreNo order by dbo.Core.CoreRevision desc) as rn
  FROM dbo.Core INNER JOIN dbo.Cable ON dbo.Cable.Id = dbo.Core.CableId
  WHERE dbo.Core.CoreRevision <= @CoreRevision
  )
  SELECT *
  FROM cte c FULL JOIN cte2 c2 ON c.TagNo = c2.TagNo
  WHERE c.rn = 1 OR c2.rn = 1

How to use multiple CTEs in a single SQL query?

Use the key word WITH once at the top. If any of your Common Table Expressions (CTE) are recursive (rCTE) you have to add the keyword RECURSIVE at the top once also, even if not all CTEs are recursive:

WITH RECURSIVE
  cte1 AS (...)         -- can still be non-recursive
, cte2 AS (SELECT ...
           UNION ALL
           SELECT ...)  -- recursive term
, cte3 AS (...)
SELECT ... FROM cte3 WHERE ...

The manual:

If RECURSIVE is specified, it allows a SELECT subquery to
reference itself by name.

Bold emphasis mine. And, even more insightful:

Another effect of RECURSIVE is that WITH queries need not be ordered:
a query can reference another one that is later in the list. (However,
circular references, or mutual recursion, are not implemented.)
Without RECURSIVE, WITH queries can only reference sibling WITH
queries that are earlier in the WITH list.

Bold emphasis mine again. Meaning that the order of WITH clauses is meaningless when the RECURSIVE key word has been used.

BTW, since cte1 and cte2 in the example are not referenced in the outer SELECT and are plain SELECT commands themselves (no collateral effects), they are never executed (unless referenced in cte3).

How can I have multiple common table expressions in a single SELECT statement?

I think it should be something like:

WITH 
    cte1 as (SELECT * from cdr.Location),
    cte2 as (SELECT * from cdr.Location)
select * from cte1 union select * from cte2

Basically, WITH is just a clause here, and like the other clauses that take lists, "," is the appropriate delimiter.

Keeping it simple and how to do multiple CTE in a query

You can have multiple CTEs in one query, as well as reuse a CTE:

WITH    cte1 AS
        (
        SELECT  1 AS id
        ),
        cte2 AS
        (
        SELECT  2 AS id
        )
SELECT  *
FROM    cte1
UNION ALL
SELECT  *
FROM    cte2
UNION ALL
SELECT  *
FROM    cte1

Note, however, that SQL Server may reevaluate the CTE each time it is accessed, so if you are using values like RAND(), NEWID() etc., they may change between the CTE calls.

combining two CTE in a single query

If you are joining values from CTE1 and CTE2 based on CHILDID column then you can write as:

SELECT 
  CTE1.CONSUMERID, 
  CTE1.CHILDID,
  CTE1.APPLICATIONUSAGESTARTDATE,
  CTE1.WEEKNUMBER,
  -- a.CHILDID    ,
   AVG(CONVERT(DECIMAl,DATEDIFF ( minute , b.TIMESTAMP , a.TIMESTAMP))) 
   as CURRENTWEEKTIMERRESTART
FROM
   CTE2 a
   LEFT  JOIN CTE2 b on a.CHILDID = b.CHILDID and a.row = b.row+1
   LEFT  JOIN CTE1   on CTE1.CHILDID = a.CHILDID and CTE1.RN = 1 
   group by CTE1.CONSUMERID,CTE1.CHILDID,CTE1.APPLICATIONUSAGESTARTDATE,CTE1.WEEKNUMBER
   ORDER BY CTE1.CHILDID ASC

Joining more than one common table expression (CTE) is taking too long in SQL (query will not end) is there something wrong with my code?

I have the answer to this question.

I have just heard back from a tech guy and it is because of indexing.

By adding an index the cost of the query went from:

cost=151491121951.08
to
cost=820386.91

You can do:

EXPLAIN SELECT primary_key as timing,
MIN(timedate) AS second_try,
FROM table t1
WHERE timedate > (SELECT MIN(timedate) FROM table t2 WHERE t1.primary_key = t2.primary_key group by primary_key)
AND criteria1='x' AND timedate BETWEEN 'x' AND 'x' GROUP BY 1

to get the cost of the query using EXPLAIN

Joining multiple CTEs

Since you only work with two tables, orders and rev, consider conditional aggregation by moving WHERE conditions to CASE logic for single aggregate query. Also, consider only one CTE for all possible country/brand pairs for LEFT JOIN on the two tables.

WITH cb AS (
  SELECT Country, Brand FROM orders
  UNION
  SELECT Country, Brand FROM rev
)

SELECT cb.Country
     , cb.Brand
     , SUM(o.netprice) AS OrdersNet
     , SUM(CASE 
               WHEN o.isopen = 1
               THEN o.netprice
           END) AS OpenOrdersNet
     , SUM(CASE 
               WHEN r.bdate BETWEEN '2020-12-01' AND '2020-12-31'
               THEN r.netprice
           END) AS Revenue
     , SUM(CASE 
               WHEN r.bdate BETWEEN '2020-12-01' AND '2020-12-31'
               THEN r.rpro
           END) AS RawProceeds
     , SUM(CASE 
              WHEN r.bdate BETWEEN '2020-11-01' AND '2020-11-30'
              THEN r.netprice
           END) AS RevenueCompare
     , SUM(CASE 
              WHEN r.bdate BETWEEN '2020-11-01' AND '2020-11-30'
              THEN r.rpro
           END) AS RawProceedsCompare    
FROM cb
LEFT JOIN orders o
 ON cb.Country = o.Country
 AND cb.Brand = o.Brand
LEFT JOIN rev r
 ON cb.Country = r.Country
 AND cb.Brand = r.Brand
GROUP BY cb.Country
       , cb.Brand

SQL Fiddle

SQL join on CTE with multiple

Consider joining by two derived tables (subqueries in FROM and JOIN clauses) as your duplicate count includes both tables and not just one:

...
SELECT q.c1, q.c2, q.c3, q.t2_c1, a.[TWO]
FROM
  (
   SELECT *
   FROM (
          SELECT  ONE, TWO,
          ROW_NUMBER() OVER(PARTITION BY ONE ORDER BY ONE) duplicate_count
          FROM all_col1
        ) sub
   WHERE sub.duplicate_count = 1 
  ) a

INNER JOIN 
  (
   SELECT t1.c1, t1.c2, t1.c3, t2.c1 as t2_c1
   FROM s1.t2 t2
   INNER JOIN s1.t1 t1 on t2.c6 = t5.c6
   WHERE t2.c5 >= '2014-01-01'
    AND t2.c5 >= '2014-01-01'
    AND t1.c4 = 'P' 
  ) q

ON a.ONE = q.c1

How to join the result set of Common Table Expression with other existing table in sql server 2005?

You can define multiple CTEs for a single select, and each CTE can reference previously defined ones. So you can do:

With CTEQuery
as
(SELECT     StudentOnlineExamCourseAnswer.StudentID, StudentOnlineExamCourseAnswer.OnlineExamID, StudentOnlineExamCourseAnswer.CourseID, 
                      StudentOnlineExamCourseAnswer.CentreID,
case QuestionBank.ComplexLevelID when 1 then (2) when 2 then (4) when 3 then (6) when 4 then (8) when 5 then (10) end as Mark
FROM         QuestionBank INNER JOIN
                      StudentOnlineExamCourseAnswer ON QuestionBank.Answer = StudentOnlineExamCourseAnswer.Answer AND 
                      QuestionBank.QuestionID = StudentOnlineExamCourseAnswer.QuestionID)
, SummarizedCTE as ( 
select StudentID, OnlineExamID ,CourseID , CentreID , sum(Mark) as TotalMark from CTEQuery 
group by StudentID, OnlineExamID ,CourseID , CentreID)
select <new query involving joining SummarizedCTE with the "other table" referenced in your discussion>

---

Related Topics