How can I use GROUP_CONCAT with MATCH but still display all items in GROUP_CONCAT?

I am currently working on a MySQL query that retrieves information from three tables in my database: category

category_industry

and industry

. They are related based on the identifier as such:

category            category_industry        industry
------------------  ------------------       ------------------
ID | Title          c_id | i_id              ID | Title
 1 | Fab Floor         1 |    1               1 | Beef
                       1 |    2               2 | Pork

      

The table category_industry

lists the industries that the category belongs to by category ID and industry. What I am doing is get the id of each industry and output the name of that industry from the table industry

. I am using operator GROUP_CONCAT

to combine headers. Without searching the query, this is how the result should look and look:

Fabrication Floor
(Beef, Pork)

      

However, if I do a search and one of the titles is matched in the search, it will only display that title, not all of them. Here's my query if I search for "Beef":

SELECT *,
    GROUP_CONCAT(i.industry_title ORDER BY i.industry_title ASC SEPARATOR ', ') AS industries,
    MATCH (c.category_title, c.category_details) AGAINST ('Beef*' IN BOOLEAN MODE) AS cscore,
    MATCH (i.industry_title) AGAINST ('Beef*' IN BOOLEAN MODE) AS iscore,
    c.category_id AS cat_id
FROM category c
LEFT JOIN category_industry t  ON t.category_id = c.category_id
LEFT JOIN industry i ON i.industry_id = t.industry_id
WHERE
    MATCH (c.category_title, c.category_details) AGAINST ('Beef*' IN BOOLEAN MODE)
    OR MATCH (i.industry_title) AGAINST ('Beef*' IN BOOLEAN MODE)
    GROUP BY c.category_id
    ORDER BY (cscore + iscore) DESC, c.added_date ASC

      

This query gives the following result:

Fabrication Floor
(Beef)

      

It will show all categories that are in the industry Beef

, but removes all other industries from the list. I wish there were still other industries showing.

Does anyone know a solution to my problem? I've tried rewriting my statements in different ways, but can't seem to get the list to stay complete. Thank you in advance.

+3


source to share