Union of select queries with cte

cte union
cte sql
recursive cte
multiple cte sql
sql union
cte in stored procedure
union all inside cte
how to loop using cte in sql server

I m trying to union two select queries which uses cte’s.

Ex:

Query1:

With cte1 as
(Select assignment_number 
From per_all_assignments_f)

Select cte1.assignment_number
From per_all_Assignments_f
Where assignment_status = ‘ACTIVE’


Query2:
With cte2 as
(Select assignment_number 
From per_all_assignments_f)

Select cte2.assignment_number
From per_all_assignments_f
Where assignment_status = ‘INACTIVE’

Now, I want to union query1 and query2. Please help. Thanks.

What's the purpose of doing it that way? Isn't it just

select assignment_number
from per_all_assignments_f
where assignment_status in ('ACTIVE', 'INACTIVE')

CTE and UNION Queries - MSDN, Each query, however, uses the same base set of records. eg: SELECT FROM Products p. JOIN -- many other tables. WHERE p. If you are trying to union multiple CTEs, then you need to declare the CTEs first and then use them: With Clients As ( Select Client_No From dbo.Decision_Data Group By Client_No Having Count(*) = 1 ) , CTE2 As ( Select Client_No From dbo.Decision_Data Group By Client_No Having Count(*) = 2 ) Select Count(*) From Decision_Data Union Select Count(Distinct Client_No) From dbo.Decision_Data Union Select Count(*) From Clients Union Select Count(*) From CTE2;

try like below

With cte1 as
(Select assignment_number ,assignment_status
From per_all_assignments_f
)
,
cte2 as
(Select assignment_number ,assignment_status
From per_all_assignments_f)
Select cte1.assignment_number
From cte1
Where assignment_status = 'ACTIVE'
union 
Select cte2.assignment_number
From cte2
Where assignment_status = 'INACTIVE'

How do you UNION with multiple CTEs?, Decision_Data Union Select Count(*) From Clients Union Select Count(*) From CTE2;. You can even use one CTE from another: With Clients� Since you can't write all 4 concurrently then try to union directly to your queries as below, Selecta, b, cFrom([Query From cte1] join [Query From cte2))UnionSelectx, y, zFrom([Query From cte3] join [Query From cte4)) Thursday, January 29, 2015 3:44 AM. Reply.

Try this: You have to comma separate the CTEs and then select from then

With cte1 as
(
    SELECT assignment_number,
           assignment_status
    FROM   per_all_assignments_f
),
cte2 as
(
    SELECT assignment_number,
           assignement_status
    FROM   per_all_assignments_f
)
SELECT  assignment_number
FROM    cte1
WHERE   assignment_status = ‘ACTIVE’
UNION -- OR UNION ALL   
SELECT  assignment_number
FROM    per_all_assignments_f
WHERE   assignment_status = ‘INACTIVE’
ORDER BY assignment_number

Using CTEs and Unions to Compute Running Totals, Bundling the query in a CTE. To get there, let's start by taking our original query and wrapping it in a CTE: with individual_performance as ( select� UNION ALL SELECT e.emp_id, e.first_name, e.manager_id, r.emplevel + 1 FROM employee e INNER JOIN cteEmp r ON e.manager_id = r.emp_id) This part is called the recursion and is the part that does the recursive query. We perform a UNION ALL to combine the results of this with the results of the first part.

WITH statement (Common Table Expressions), A recursive sub-query references the CTE being defined. WITH recursive_cte AS ( <initial subquery> UNION ALL <recursive subquery> ) SELECT … Example 1:� Paste the SQL statement for the select query into the SQL view object tab of the union query. Delete the semicolon (;) at the end of the select query SQL statement. Press Enter to move the cursor down one line, and then type UNION on the new line. Click the tab for the next select query that you want to combine in the union query.

Recursive CTEs Explained, A recursive CTE must contain a UNION ALL statement and, to be recursive, have a second query definition that references the CTE itself. Let's look at a simple� As per the CTE Syntax each CTE query will start with a “With” followed by the CTE Expression name with column list. Here we have been using only one column as ROWNO. Next is the Query part, here we write our select query to be execute for our CTE.

Understanding SQL Server Recursive CTE By Practical Examples, SQL Server recursive common table expression (CTE) to query hierarchical The recursive member is union-ed with the anchor member using the UNION� The SQL UNION Operator. The UNION operator is used to combine the result-set of two or more SELECT statements. Each SELECT statement within UNION must have the same number of columns; The columns must also have similar data types; The columns in each SELECT statement must also be in the same order; UNION Syntax

Comments
  • (1) Which of the two DBMS are you using, MySQL or Oracle? (2) You've completely messed up your examples. You are only selecting assignment_number in the CTEs, but in your query you are not selecting from the CTEs, but from the original tables with another column (assignment_status). You may want to correct this. Are the two CTEs identical?
  • Sorry zaynul. I m new to oracle sql and It is my first try here. However got my answer. Thanks for that searle. I ll try to be more specific from next time.
  • With cte1 as ( SELECT assignment_number, assignment_status FROM per_all_assignments_f ), cte2 as ( SELECT assignment_number, assignement_status FROM per_all_assignments_f ) SELECT assignment_number FROM cte1 WHERE assignment_status = ‘ACTIVE’ UNION -- OR UNION ALL SELECT cte2.assignment_number FROM per_all_assignments_f WHERE assignment_status = ‘INACTIVE’ This is working. Now I want to get the result order by the assignment_number. In this case how to achieve it? Can you please help.
  • If you want to sort rows, add an ORDER BY clause at the end of the query.
  • Whilst this certainly a better query, I suspect the OP's query is simplified for brevity
  • Hi Zaynul, in the actual select queries, I m using multiple tables and joins. Just to simplify my scenario, I have used the above query as an example
  • @maxsamuel there is no problem to use multiple tables and join
  • Thank you Searle. It is working. I just have one more issue on the above query. I want to give order by assignment_number. Can u help me?