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.
source to share
No one has answered this question yet
See similar questions:
or similar: