MySQL left join with multiple rows on right, how to combine to one row for each on left (with the field I want from the right table concated)?

mysql

Solution

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.

Problem

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. 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): ``` 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 `concat`ed, 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.

Original source