MySQL JOIN needs to return all categories the product is in, per product, even if only one category is searched

I have the following tables defined:

CREATE TABLE products (
    product_id INTEGER(11) NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    section VARCHAR(255) DEFAULT NULL,
    PRIMARY KEY (product_id)
) ENGINE=MyISAM;

CREATE TABLE categories (
    category_id INTEGER(11) NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    PRIMARY KEY (category_id)
) ENGINE=MyISAM;

CREATE TABLE product_categories (
    product_id INTEGER(11) NOT NULL,
    category_id INTEGER(11) NOT NULL,
    PRIMARY KEY (product_id, category_id)
) ENGINE=MyISAM;

      

        
        
        
      

    

There is actually much more, and this is part of optimizing much more complex and complex queries. Part of it moves some of the slow subqueries into views, which has helped so far.

Queries got very slow when I added category tables / product_categories and joined them when allowing users to search by products .section or categories.category_id. The UI passes them in as search parameters and I am trying to get a string for each product with its ID, name, section and comma separated list of category names associated with it. I was able to do it much faster with the following look and query:

CREATE OR REPLACE
ALGORITHM = MERGE
VIEW view_products_with_categories
AS
SELECT 
    p.product_id,
    p.name,
    p.section,
    pc.name AS category
products p
LEFT JOIN product_categories pc on p.product_id = pc.product_id
LEFT JOIN categories c ON pc.category_id = c.category_id;

SELECT
    product_id,
    name,
    section,
    GROUP_CONCAT(DISTINCT category ORDER BY category) AS categories
FROM view_products_with_categories
GROUP BY product_id;

      

        
        
        
      

    

Let's say we have the following lines:

product_id  name               section          category_id category
332913      Model Train Engine child-and-baby   1160        child-and-baby>baby-and-pre-schooltoys>playsets
332913      Model Train Engine child-and-baby   1308        toys>baby-and-preschool>playsets
332913      Model Train Engine child-and-baby   1312        toys>carstrains-and-planes>cars-and-vehicles

      

        
        
        
      

    

A simple query above gives me this:

product_id  name                section         categories
332913      Model Train Engine  child-and-baby  child-and-baby>baby-and-pre-schooltoys>playsets,toys>baby-and-preschool>playsets,toys>carstrains-and-planes>cars-and-vehicles

      

        
        
        
      

    

This is fine and as expected. However, I would like the user to be able to search by category. Currently, our UI is doing some auto-completion magic on category names and adding a filter to the dynamically generated SQL with the category in it. If I left the category_id in the GROUP_CONCAT query, it would be 1160. Let's say they wanted to find the second one (1308), so we modify the query like this:

SELECT
    product_id,
    name,
    section,
    GROUP_CONCAT(DISTINCT category ORDER BY category) AS categories
FROM view_products_with_categories
WHERE category_id = 1308
GROUP BY product_id;

      

        
        
        
      

    

We now get the following:

product_id  name                section         categories
332913      Model Train Engine  child-and-baby  toys>baby-and-preschool>playsets

      

        
        
        
      

    

Again, exactly what you expect. However, the customer wants to see all the categories associated with a product that has one or more of the categories they are looking for. So let's do some simplified data samples to show you what I'm looking for:

product_id  name       section     category_id  category
1           product_1  section_1   1            category_1
1           product_1  section_1   2            category_2 
1           product_1  section_1   3            category_3
2           product_2  section_2   3            category_3
2           product_2  section_2   4            category_4 
2           product_2  section_2   5            category_5

      

        
        
        
      

    

If the user is looking for category_id = 3, I would like them to get this:

product_id  name      section   categories
1           product_1 section_1 category_1, category_2, category_3 
2           product_2 section_2 category_3, category_4, category_5 

      

        
        
        
      

    

But I am currently getting:

product_id  name      section   categories
1           product_1 section_1 category_3 
2           product_2 section_2 category_3

      

        
        
        
      

    

I just can't figure out how to do this without the subquery, the slowness of which is the reason I go to views in the first place. I hope there is something blindingly obvious there that I am missing, possibly due to sleep deprivation, so any help would be appreciated.

UPDATE . I should also mention that it is possible that the product is not in any categories, hence the LEFT JOINs in my code.