Reputation: 504
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
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