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)?

Question

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 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.

Answer 1

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.