CODEWITHSUNDEEP
mysqlsqlsubquery
Richard Skinner
Richard Skinner

Reputation: 738

MySQL GROUP_CONCAT using sub-queries

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

Upvotes: 1

Views: 115

Answers (1)

M Khalid Junaid
M Khalid Junaid

Reputation: 64496

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

Upvotes: 1

Related Questions