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