1

I've this query

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;

This is how to result looks like

enter image description here

As you can see it returns four columns. I want to merge distinct data of all four into one column.

I looked at similar questions but none of those has Left Joins in their query. I know it can be possible using union but I'm struggling.

Thanks.

Pranav
  • 447
  • 5
  • 18
  • What do you mean distinct data? Distinct combination of categoryid 1,2,3,4? You could just nest your original query inside another query, where you select DISTINCT * from ( /*inner query*/). – user3538411 Jul 03 '15 at 16:40
  • @user3538411 There are four columns which could contain duplicate data. I want the distinct data from all the columns into one. I updated the question with query result. Have a look. – Pranav Jul 03 '15 at 16:49
  • Hi Pranav. Were you able to solve this issue.I am also facing the same problem with converting the query from "with" to mysql version. – A_01 Aug 17 '17 at 08:52

1 Answers1

0

As you said, you could use a UNION over the result of your query. In the rest of the answer I will show you a solution using CTE, which is a standard SQL syntax available in many systems, but, unfortunately, not in MySQL. However, to convert this query for MySQL you can look at the following answer: How do you use the "WITH" clause in MySQL?

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;
Community
  • 1
  • 1
Renzo
  • 26,848
  • 5
  • 49
  • 61
  • Thanks for answering! Although I'm struggling to convert but I'll look into it. Thanks, again. – Pranav Jul 03 '15 at 20:06