table joins with multiple group_concat

Question

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.

I think you may have mistyped something because I do not get the same output with the same input/query (specifically there are two ord_id = 100); — Explosion Pills, Dec 04 '12 at 19:34
This is likely due to MySQL's lenient `GROUP BY` rules acting over more row groups than your actually see in the output. You only have `GROUP BY order_id`, but you have other cols in your `SELECT`. Fix the data as suggested by @ExplosionPills and then we can help work out the query. — Michael Berkowski, Dec 04 '12 at 19:36
Look at this http://sqlfiddle.com/#!2/31b58/4 for example. When adding `instruction` into the `GROUP BY` it becomes apparent that you are working over 2 rows — Michael Berkowski, Dec 04 '12 at 19:41
i have edited my question, please check it out specially the instruction field. — WreckTangle, Dec 04 '12 at 19:55
In the cancelled_item_table you have repeated rows. Doing a join with rows like this makes results repeat. I think you should insert another parameter in this table or try using DISTINCT — Leandro Barreto, Dec 04 '12 at 20:05

fthiella · Accepted Answer · 2012-12-04T20:23:06.870

3

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

edited Dec 04 '12 at 20:23

answered Dec 04 '12 at 20:15

fthiella

48,073
15
90
106

you just nailed it sir, thanks everyone for your help, i appreciate it so much. – WreckTangle Dec 04 '12 at 20:39
I am having a similar problem. I don't suppose you could expand a little (in terms of explanation) as to what this sub query does? It works, but I don't really understand why. Also does this not negate the benefit of having a single query as opposed to doing multiple queries and foreach loops (php), as there are now two queries?? Thanks – Thomas Clowes Apr 09 '13 at 17:17

table joins with multiple group_concat

1 Answers1