CODEWITHSUNDEEP
mysqlsql
Tarun Baraiya
Tarun Baraiya

Reputation: 504

get multiple category in a single row

i have table company data is :

id | name | email
 1 | abc  | [email protected]
 2 | xyz  | [email protected]

other table of category data is :

id | name
1  | cat1
2  | cat2
3  | cat3

and joined table of both category and company is join_cat_company

category_id | company_id | main
          1 |           1| 1
          2 |           1| 0
          3 |           1| 0
          2 |           2| 1
          1 |           2| 0
          3 |           2| 0

in joined table main is use for main category

i want result like :

  company_name | category
           abc | cat1,cat2,cat3
           xyz | cat2,cat1,cat3

the main category must be fist of the category list

Upvotes: 1

Views: 178

Answers (2)

eggyal
eggyal

Reputation: 126065

SELECT   company.name AS company_name,
         CONCAT_WS(',',
           GROUP_CONCAT(  maincat.name),
           GROUP_CONCAT(othercats.name)
         ) AS category
FROM     join_cat_company
       JOIN company                ON join_cat_company.company_id  =   company.id
  LEFT JOIN category AS maincat    ON join_cat_company.category_id =   maincat.id
                                  AND join_cat_company.main = 1
  LEFT JOIN category AS othercats  ON join_cat_company.category_id = othercats.id
                                  AND join_cat_company.main = 0
GROUP BY company.name

See it on sqlfiddle.

EDIT

Further to @lanzz's excellent observation above, you could simply do:

SELECT   company.name AS company_name,
         GROUP_CONCAT(
           category.name ORDER BY join_cat_company.main DESC
         ) AS category
FROM     join_cat_company
       JOIN company  ON  company.id = join_cat_company.company_id
  LEFT JOIN category ON category.id = join_cat_company.category_id
GROUP BY company.name

See it on sqlfiddle.

Upvotes: 2

lanzz
lanzz

Reputation: 43228

You're looking for the GROUP_CONCAT function.

Upvotes: 2

Related Questions