How can I convert this SQL query that uses the WITH clause for MySQL?
How can I convert this to MySQL? It uses a proposal WITH
that is not supported by MySQL. I searched the site and read a few questions but didn't help.
I'm still learning SQL, so I don't know much: /.
This query combines data from four columns into one.
WITH query AS (
SELECT t1.categoryid AS lev1, t2.categoryid as lev2, t3.categoryid as lev3, t4.categoryid as lev4
FROM category AS t1
LEFT JOIN category AS t2 ON t2.parent = t1.categoryid
LEFT JOIN category AS t3 ON t3.parent = t2.categoryid
LEFT JOIN category AS t4 ON t4.parent = t3.categoryid
WHERE t1.categoryid = 4149418031)
SELECT lev1 AS category_value FROM query
UNION
SELECT lev2 AS category_value FROM query
UNION
SELECT lev3 AS category_value FROM query
UNION
SELECT lev4 AS category_value FROM query;
+3
source to share
2 answers
There are several ways. I would suggest using cross join
conventions in select
:
SELECT DISTINCT (case when n.n = 1 then t1.categoryid
when n.n = 2 then t2.categoryid
when n.n = 3 then t3.categoryid
when n.n = 4 then t4.categoryid
end) as category_value
FROM category t1 LEFT JOIN
category t2
ON t2.parent = t1.categoryid LEFT JOIN
category t3
ON t3.parent = t2.categoryid LEFT JOIN
category t4
ON t4.parent = t3.categoryid CROSS JOIN
(select 1 as n union all select 2 union all select 3 union all select 4) n
WHERE t1.categoryid = 4149418031;
+4
source to share
WITH
creates (effectively) a temporary table for the query, so one approach is:
CREATE TEMPORARY TABLE IF NOT EXISTS query AS (SELECT t1.categoryid AS lev1, t2.categoryid as lev2, t3.categoryid as lev3, t4.categoryid as lev4
FROM category AS t1
LEFT JOIN category AS t2 ON t2.parent = t1.categoryid
LEFT JOIN category AS t3 ON t3.parent = t2.categoryid
LEFT JOIN category AS t4 ON t4.parent = t3.categoryid
WHERE t1.categoryid = 4149418031))
SELECT lev1 AS category_value FROM query
UNION
SELECT lev2 AS category_value FROM query
UNION
SELECT lev3 AS category_value FROM query
UNION
SELECT lev4 AS category_value FROM query;
+3
source to share