Using GROUP_CONCAT in separate row records

Question

I'm having trouble using GROUP_CONCAT. I'm pretty sure this is the only way to get what I want but it doesn't seem give me the results I need.

Here is my statement:

SELECT
   b.*,
   GROUP_CONCAT(c.finance_code) AS finance_codes
FROM
   `oc_finance_breakpoints` b
   LEFT JOIN
      `oc_finance_breakpoints_codes` c ON c.breakpoint_id = b.breakpoint_id;

This will gather data in the finance_breakpoints table, structure below:

breakpoint_id
from_value
to_value
minimum_deposit

As well as multiple "finance codes" from my join table, finance_breakpoint_codes:

breakpoint_code_id
breakpoint_id
finance_code

There can be, are are likely to be, several finance codes to a breakpoint. When I run the sql when there is only one entry, I get the following:

1 | 280.00 | 750.00 | 10 | ONIF6,ONIF10,ONIF12

But if there are two entries in the breakpoints table, all that happens is it tacks the additional finance codes onto the end of the above, meaning I only ever get one row with the first set of data, and all the finance codes in one column.

Ideally I'd like it to return something such as this:

1 | 280.00 | 750.00 | 10 | ONIF6,ONIF10,ONIF12

2 | 750.00 | 1500.00 | 10 | ONIB12-9.9,ONIB24-9.9,ONIB36-9

Rather than:

1 | 280.00 | 750.00 | 10 | ONIF6,ONIF10,ONIF12,ONIB12-9.9,ONIB24-9.9,ONIB36-9

Is there any way of achieving what I want? Am I maybe using the wrong function?

Answer 1

The use of an aggregate function (such as GROUP_CONCAT) in your query ensures that it will return aggregated results, while the absence of an explicit grouping ensures that it will return a single, overall summary row.

You need to add a group by clause to the end of your query - like so:

SELECT
   b.*,
   GROUP_CONCAT(c.finance_code) AS finance_codes
FROM
   `oc_finance_breakpoints` b
   LEFT JOIN `oc_finance_breakpoints_codes` c 
          ON c.breakpoint_id = b.breakpoint_id
GROUP BY b.breakpoint_id