How to fix this using GROUP_CONCAT() fun in mysql

Databases
1

Hi there I working on an e-commerce project and want to fetch all sub categories along with their parent categories
this is my SQL query

SELECT c.category_id cat_id,c.category_name cat_name,s.subcategory_id,s.name FROM sub_category s LEFT JOIN category c ON c.category_id = s.category_id GROUP BY s.subcategory_id

And here is the output:

db_error-ewacth
db_error-ewacth1335Ă—329 54.1 KB

I want to remove duplicates by using GROUP CONCAT

2

okay, i know what you mean even though there aren’t any duplicates ;o)

first of all, let’s start with this –

FROM sub_category s LEFT JOIN category c

this LEFT JOIN is structured so that it will return all subcategories even if they don’t have a parent category

i’m pretty sure you want this –

FROM category c LEFT JOIN sub_category s

this LEFT JOIN is structured so that it will return all categories even if they don’t have any child subcategories

and now what you want is easier… you want one result row per category, with subcategories handled by GROUP_CONCAT

SELECT c.category_id    AS cat_id
     , c.category_name  AS cat_name
     , GROUP_CONCAT(
             CONCAT( s.subcategory_id
                   , '|'
                   , s.name) ) AS subcategories
  FROM category AS c
LEFT OUTER
  JOIN sub_category AS s  
    ON s.category_id = c.category_id 
GROUP 
    BY c.category_id    
     , c.category_name
1 Like
3

Yes Exactly this is what I want!

4

@r937 Thanks you Bro

5

One more thing I want to ask you is that “I am very bad in SQL queries I am recently pass out my highschool all thing about web programming I know due Self learning from youtube,udemy and these kind of platform and more peoples like you So, if it’s possible could you please recommend me a SQL Book so I can improve my SQL knowledge”

6

yes, i sure can

“Simply SQL” available from sitepoint.com

i am not sure about the various ways to purchase it, and i think you can even find it on Amazon and other sites

but that’s the book i would recommend… absolutely first class stuff

:sunglasses:

1 Like
7

Not that you’re biased or anything :winky:

(It is a good book, though…)

1 Like
8

Thank alot one more time :innocent: InshahAllah I read this book :+1:

9

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.