Convert CTE Query to Ordinary MySQL Query

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.

Solution

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