MySQL MySQL加入多对多单行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5158950/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL join many to many single row
提问by Nathan Stanford II
I have 3 tables that I want to combine, see below for details:
我有 3 个要合并的表，详情见下文：
product
产品
- productID
- name
- price
- 产品编号
- 姓名
- 价钱
prod_cat
prod_cat
- productID
- categoryID
- 产品编号
- 类别ID
category
类别
- categoryID
- name
- 类别ID
- 姓名
joined
加入
product.productID category.categoryID product.name product.price category.name(each one though, since a product can belong to more than one category)
product.productID category.categoryID product.name product.price category.name（虽然每一个，因为一个产品可以属于多个类别）
What I want to do is get each product with the categories that relate to them in a single query. How would I got about this?
我想要做的是在单个查询中获取每个产品与它们相关的类别。我怎么会知道这件事？
回答by Mark Byers
You need two joins:
您需要两个连接：
SELECT
product.productID,
category.categoryID,
product.name,
product.price,
category.name
FROM product
JOIN product_cat ON product.productID = product_cat.productID
JOIN category ON category.categoryID = product_cat.categoryID
If a product could be in no categories and you still want to return it, change JOIN to LEFT JOIN in both places.
如果产品不属于任何类别，但您仍想退货，请将两个地方的 JOIN 更改为 LEFT JOIN。
An alternative approach:
另一种方法：
SELECT
product.productID,
product.name,
product.price,
GROUP_CONCAT(category.name)
FROM product
JOIN product_cat ON product.productID = product_cat.productID
JOIN category ON category.categoryID = product_cat.categoryID
GROUP BY product.productID
However it might be better just to use two queries instead of putting multiple values into a single cell.
但是，最好仅使用两个查询而不是将多个值放入单个单元格中。
回答by Orbit
You can use group_concat if using mySQL...
如果使用 mySQL，您可以使用 group_concat ...
see this thread.
看到这个线程。
SQL to join one table to another table multiple times? (Mapping products to categories)
(possible duplicate)
（可能重复）
