table joins with multiple group_concat

group-by, group-concat, mysql

Solution

Since the join with `cancelled_item_table` multiplies rows, you have to join to an already grouped subquery, like this:

SELECT
  ord.item_cd,
  ord.order_id,
  ord.descs,
  ord.quantity - coalesce(x.tot,0) as quantity,
  GROUP_CONCAT(i.instruction) as instruct,
  x.cancelled
FROM
  table_orders ord LEFT JOIN table_instructions i
  ON ord.item_cd = i.item_cd LEFT JOIN
  (select seq_no, count(*) as tot, GROUP_CONCAT(quantity) as cancelled
   from cancelled_item_table
   group by seq_no) x ON ord.seq_no = x.seq_no
WHERE ord.status = 'A'
GROUP BY ord.item_cd, ord.order_id, ord.descs, quantity

Problem

I have a problem regarding joining tables with group_concat. Here are the details. table_orders: ``` item_cd order_id descs quantity status seq_no 1 100 coca-cola 2 A 232 2 100 pizza 1 A 233 3 101 cheeseburger 5 A 234 4 102 pepsi 4 A 235 4 ``` table_instructions: ``` item_cd instruction 3 more cheese 3 less vegetable ``` cancelled_item_table: ``` quantity seq_no 1 234 1 234 1 235 ``` Now what I want to achieve is like this: ``` item_cd descs quantity instructions cancelled_item 1 coca-cola 2 - - 2 pizza 1 - - 3 cheeseburger 2 more cheese, less vegetable 1,1 4 pepsi 4 - 1 ``` This is my current query: ``` SELECT ord.item_cd, ord.order_id, ord.descs, ord.quantity, GROUP_CONCAT(x.quantity) as cancelled, GROUP_CONCAT(i.instruction) as instruct FROM table_orders ord LEFT JOIN cancelled_item_table x ON ord.seq_no = x.seq_no LEFT JOIN table_instructions i ON ord.item_cd = i.item_cd WHERE ord.status = 'A' GROUP BY ord.order_id ``` and here is the output: ``` item_cd descs quantity instructions cancelled_item 1 coca-cola 2 - 1 2 pizza 1 - 1 3 cheeseburger 2 more cheese, more cheese, less vegetable, less vegetable 1,1,1,1 4 pepsi 4 - 1 ``` If you notice, cheeseburger has 2 cancelled item and 2 instruction, but the output is 4, looks like it's multiplying.

Original source