如何获取每个产品的所有变体，并提取多次获取产品名称

Question

我有3个表格。

“table_products”

product_id product_name 
1   A 
2   B 
3   C 
4   D 
5   E 

“table_varients”

variant_id variant_name 
1   v1 
2   v2 
3   v3 
4   v4 
5   v5 

“table_product_varients”

product_id variant_id 
1   1 
1   2 
1   3 
2   3 
2   4 
3   1 
3   4 
4   1 
5   1 
5   2 

，我想从选择这样的查询结果：

A - V1, V2, V3 
B - V3, V4 
C - V1, V4 
D - V1 
E - V1, V2  

Devesh

Answer 1

简单连接，然后GROUP_CONCAT

SELECT a.product_name, GROUP_CONCAT(c.variant_name) 
FROM table_products a 
INNER JOIN table_product_varients b ON a.product_id = b.product_id 
INNER JOIN table_varients c ON b.variant_id = c.variant_id 
GROUP BY a.product_name