CODEWITHSUNDEEP
mysqlsql
Joe King
Joe King

Reputation: 3021

How to avoid repeating code in common table expressions in MySQL?

I have a query that looks like this:

WITH 
cte1 AS
   (SELECT DISTINCT id FROM (<Query1>) t1) ,
cte2 AS
   (<Query1>)
SELECT COUNT(DISTINCT cte2.id)
  FROM cte1
    LEFT JOIN cte2 ON <various conditions>

Is there any way to avoid repeating <Query1> in both common table expressions ?

Upvotes: 0

Views: 59

Answers (1)

The Impaler
The Impaler

Reputation: 48770

You are already almost doing it. Rephrase the query as:

WITH
cte2 AS
   (<Query1>),
cte1 AS
   (SELECT DISTINCT id FROM cte2)
SELECT COUNT(DISTINCT cte2.id)
FROM cte1
LEFT JOIN cte2 ON <various conditions>

Upvotes: 3

Related Questions