mysql - 显示带有类别的产品列表不会显示未耦合的类别

Question

我有 3 个表、产品、类别和 Couple_product_category我想要数据库中的所有产品以及一系列耦合类别。但是，这工作正常，当我添加尚未与类别耦合的新产品时，不会返回。

我想要最快的方法来阻止性能。这就是我现在所拥有的:

SELECT 
   product.id, 
   product.product_name, 
   GROUP_CONCAT(productcategory.name SEPARATOR '; ') AS categories
FROM
   m_catalog_products AS product
LEFT JOIN 
   couple_product_category AS category
ON 
   category.product_id = product.id
INNER JOIN 
   m_catalog_productcategoy AS productcategory
ON 
   productcategory.id = category.category_id
GROUP BY 
   product.id
ORDER BY product.id, product.is_active ASC

这将导致类似的结果:

ID  Name       Categories
--  ---------  ----------------------
1   Product A  Category A; Category B
2   Product B  Category C

但是，如果产品 C 尚未与任何类别关联，它应该显示为:

ID  Name       Categories
--  ---------  ----------------------
1   Product A  Category A; Category B
2   Product B  Category C
3   Product C  

Answer 1

LEFT JOIN m_catalog_productcategoy 而不是 INNER JOIN:

SELECT 
   p.id, 
   p.product_name, 
   GROUP_CONCAT(pc.name SEPARATOR '; ') AS categories
FROM m_catalog_products AS p
LEFT JOIN couple_product_category AS c ON c.product_id = p.id
LEFT JOIN m_catalog_productcategoy AS pc ON pc.id = c.category_id
GROUP BY  p.id
ORDER BY p.id, p.is_active ASC;