MySQL GROUP_CONCAT using sub-queries

Question

I have a GROUP_CONCAT using a sub-query however the sub-query is throwing an error 'unknown column m.itemid...'.

I think this maybe a scope issue but i am having major problems finding a solution with my limited knowledge of MySQL. ANy help is appreciated and if you need more information please let me know.

SELECT *, 
       CASE parent_item_id 
         WHEN 0 THEN itemid 
         ELSE parent_item_id 
       end  AS order_items_sort, 
       (SELECT Group_concat(id) 
        FROM   (SELECT i.install_delivery_id `id` 
                FROM   machine_serials s 
                       INNER JOIN install_delivery_items i 
                               ON s.install_delivery_item_id = i.id 
                WHERE  s.machine_id = m.itemid 
                GROUP  BY i.install_delivery_id) g) `delivery_id` 
FROM   machines m 
WHERE  m.orderid = 36549 
ORDER  BY order_items_sort, parent_item_id ASC

M Khalid Junaid · Accepted Answer · 2014-05-15 13:58:56Z

1

I guess you can use joins in your outer query this way you don't need to do the correlated subquery

SELECT m.*, 
(CASE parent_item_id 
   WHEN 0 THEN itemid 
   ELSE parent_item_id
END) AS order_items_sort, 
GROUP_CONCAT(DISTINCT i.install_delivery_id)   `delivery_id`
FROM machines m
LEFT JOIN machine_serials s ON(s.machine_id = m.itemid)
LEFT JOIN install_delivery_items i ON s.install_delivery_item_id = i.id
WHERE m.orderid = 36549
GROUP BY m.itemid
ORDER BY order_items_sort, m.parent_item_id ASC

answered May 15, 2014 at 13:58

M Khalid Junaid

64.6k10 gold badges93 silver badges120 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL GROUP_CONCAT using sub-queries

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related