CODEWITHSUNDEEP
mysql
Pranav
Pranav

Reputation: 447

How can I convert this SQL Query which uses WITH clause to MySQL?

How can I convert this to MySQL? It uses the WITH clause which is not supported by MySQL. I've searched the site and read few questions but no help.

I'm still learning SQL so don't know much :/.

This query unions the 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;

Upvotes: 3

Views: 944

Answers (2)

Stan
Stan

Reputation: 983

WITH creates (effectively) a temporary table for the query, so one approach would be:

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;

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269923

There are several ways. I would suggest using a cross join and conditionals in the 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;

Upvotes: 4

Related Questions