Convert CTE Query to Ordinary MySQL Query-CodePudding

I am trying to convert CTE query to an ordinary MySQL query since I have issues implement it using CodeIgniter 3 Query Class with active record style to my code block.

Query with CTE

WITH CTE
     AS (SELECT id,
                name,
                Max(build) AS build
         FROM   mytable
         WHERE  build <= 6
                AND ` id ` = /*'id'*/
         GROUP  BY name) SELECT *
FROM   CTE
UNION
SELECT id,
       name,
       Min(build)
FROM   mytable
WHERE  name NOT IN (SELECT name
                    FROM   CTE)
       AND ` id ` = /*'id'*/
GROUP  BY name

What I tried;

SELECT *
FROM   (SELECT id,
               name,
               Max(build) AS build
        FROM   mytable
        WHERE  build <= 6
               AND ` id ` = /*'id'*/
        GROUP  BY name) AS cte
UNION
SELECT id,
       name,
       Min(build)
FROM   mytable
WHERE  name NOT IN (SELECT name
                    FROM   cte)
       AND ` id ` = /*'id'*/
GROUP  BY name

When I try to run query above, it doesn't recognize table cte in SELECT NAME FROM cte part. Is there any way to fix this in an efficient way?

Thanks in advance.

CodePudding user response：

I think that it would be more efficient to have only 2 queries those are used for UNION. Since the aggregation function MIN is used for the alternative build value selection, the HAVING clause can be used here.

Finally query can look like this

SELECT id,
       name,
       MAX(build) AS build
FROM   mytable
WHERE  build <= 6 AND id = /*id*/
GROUP  BY id, name
UNION
SELECT id,
       name,
       MIN(build) AS build
FROM   mytable t
WHERE id = /*id*/
      AND NOT EXISTS (
          SELECT 1 FROM mytable tt 
          WHERE tt.id = t.id AND t.build <= 6
      )
GROUP  BY id, name 
HAVING MIN(build) > 6

CodePudding user response：

I guess you can just duplicate the definition of CTE, and then simplify it. I think it comes down to:

SELECT id,
       name,
       Max(build) AS build
FROM   mytable
WHERE  build <= 6
       AND ` id ` = /*'id'*/
GROUP  BY name
UNION
SELECT id,
       name,
       Min(build)
FROM   mytable
WHERE  name NOT IN (SELECT name
           FROM   mytable
           WHERE  build <= 6
           AND ` id ` = /*'id'*/
           )
       AND ` id ` = /*'id'*/
GROUP  BY name

MySQL will figure out the most efficient query plan, so it hopefully does not need to query things twice.

CodePudding user response：

You can try as

SELECT *
 FROM   (SELECT id,
           name,
           Max(build) AS build
    FROM   mytable
    WHERE  build <= 6
           AND ` id ` = /*'id'*/
    GROUP  BY name) AS cte
UNION
   SELECT id,
   name,
   Min(build)
FROM   mytable
WHERE  name NOT IN (

    SELECT name,
           Max(build) AS build
    FROM   mytable
    WHERE  build <= 6
           AND ` id ` = /*'id'*/
    GROUP  BY name
  )
   AND ` id ` = /*'id'*/
   GROUP  BY name