Duplicate results from mysql query

user_4667025 0
Viewed 71

After query execution, we are getting duplicate id's when trying to fetch tree-structured category id. We are expecting an output without having duplicate id's

SELECT 
  GROUP_CONCAT(CONCAT_WS(',',t1.CategoryID,t2.CategoryID,t3.`CategoryID`, t4.CategoryID)) AS id 
FROM prodcategory AS t1 
LEFT JOIN prodcategory AS t2 ON t2.ParentCategoryID = t1.CategoryID 
LEFT JOIN prodcategory AS t3 ON t3.ParentCategoryID = t2.CategoryID 
LEFT JOIN prodcategory AS t4 ON t4.ParentCategoryID=t3.CategoryID 
WHERE t1.ParentCategoryID =41

actual output : 

190,256,190,257,191,261,300,191,262,192,267

expected output : 

190,256,257,191,261,300,262,192,267

sample db data : 

CategoryID |  CategoryName |  ParentCategoryID
   41      |       a       |        1
   190     |       b       |        41
   191     |       c       |        41
   192     |       d       |        41
   256     |       e       |        190
   257     |       f       |        190
   261     |       g       |        191
   262     |       h       |        191
   300     |       i       |        261
   267     |       j       |        192
mysql
1 Answers

You can use DISTINCT key word for duplicate results.

    SELECT GROUP_CONCAT(CONCAT_WS(',',DISTINCT 
    t1.CategoryID,DISTINCT t2.CategoryID,DISTINCT t3.`CategoryID`,DISTINCT  
    t4.CategoryID)) AS id FROM prodcategory AS t1 
    LEFT JOIN prodcategory AS t2 ON t2.ParentCategoryID = t1.CategoryID 
    LEFT JOIN prodcategory AS t3 ON t3.ParentCategoryID = t2.CategoryID 
    LEFT JOIN prodcategory AS t4 ON t4.ParentCategoryID=t3.CategoryID 
    WHERE t1.ParentCategoryID =41
user_95244450
Related