MySQL加入多对多单行 [英] MySQL join many to many single row

问题描述

我有3个表要合并，请参见下面的详细信息:

I have 3 tables that I want to combine, see below for details:

• productID
• 名称
• 价格

• productID
• categoryID

• categoryID
• 名称

product.productID category.categoryID product.name product.price category.name(尽管每个，因为一种产品可以属于多个类别)

product.productID category.categoryID product.name product.price category.name(each one though, since a product can belong to more than one category)

我想要做的是在单个查询中获得具有与它们相关的类别的每个产品.我怎么会这样?

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?

推荐答案

您需要两个联接:

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

如果某个产品可能没有分类，但您仍想退货，则在两个地方都将JOIN更改为LEFT JOIN.

If a product could be in no categories and you still want to return it, change JOIN to LEFT JOIN in both places.

另一种方法:

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.

这篇关于MySQL加入多对多单行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！