I have two MySQL tables, products, and barcodes. A product may have multiple barcodes, which is why I've separated it off into it's own table.
我有两个MySQL表，产品和条形码。一个产品可能有多个条形码，这就是为什么我把它分成它自己的表。
This is what I've tried (using CodeIgnighter's Active Record, but I've written out the query here, meaning if there is a typo, it might not be in my actual query):
这是我尝试过的（使用CodeIgnighter的Active Record，但我在这里写出了查询，这意味着如果有拼写错误，它可能不在我的实际查询中）：
SELECT products.id, products.snipe_price, group_concat(barcodes.barcode) as barcodes FROM products LEFT JOIN barcodes on barcodes.product_id = products.id
But this just returns one row with all of the barcodes from every product concated, how can I get one row for each product with the products barcodes?
但是这只返回一行，每个产品的所有条形码都已合并，如何使用产品条形码为每个产品获得一行？
I'd rather not have to split it up, but if there is no solution with join then please let me know.
我宁愿不必拆分它，但如果没有加入的解决方案那么请告诉我。
1 个解决方案
#1
23
You need a group by:
你需要一个小组：
SELECT products.id, products.snipe_price, group_concat(barcodes.barcode) as barcodes
FROM products LEFT JOIN
barcodes
on barcodes.product_id = products.id
group by products.id;
Without the group by, MySQL interprets the whole query as an aggregation query to summarize all the data (because of the presence of group_concat(), an aggregation function). Hence it returns only one row, summarized on all the data.
如果没有group by，MySQL会将整个查询解释为聚合查询，以汇总所有数据（因为存在group_concat（），聚合函数）。因此它只返回一行，汇总所有数据。
#1
23
You need a group by:
你需要一个小组：
SELECT products.id, products.snipe_price, group_concat(barcodes.barcode) as barcodes
FROM products LEFT JOIN
barcodes
on barcodes.product_id = products.id
group by products.id;
Without the group by, MySQL interprets the whole query as an aggregation query to summarize all the data (because of the presence of group_concat(), an aggregation function). Hence it returns only one row, summarized on all the data.
如果没有group by，MySQL会将整个查询解释为聚合查询，以汇总所有数据（因为存在group_concat（），聚合函数）。因此它只返回一行，汇总所有数据。