Sample Header Ad - 728x90

Group_concat on IF Select MYSQL

0 votes
1 answer
1285 views
I have a query as below:
SELECT o.order_id,
       c.customer_name,
       Group_concat(p.product_name, ' x', p.product_quantity ORDER BY
       receipt_order ASC
       SEPARATOR
       '
') AS items, order_status, o.delivery_collection, o.order_total, o.payment_method, o.discount_value, o.order_time, o.order_note, IF(o.delivery_collection = 'delivery', (SELECT Group_concat(address_1, address_2, postcode SEPARATOR '
') AS delAdd FROM address a WHERE c.customer_id = a.customer_id AND c.customer_type = a.customer_type), NULL) FROM orders o, order_products p, customer c WHERE c.customer_id = o.customer_id AND c.customer_type = o.customer_type AND o.order_id = p.order_id AND order_time > Now() - INTERVAL 72 hour GROUP BY p.order_id ORDER BY order_status DESC, o.order_id DESC
The problem I have is that the Group_Concat() inside the IF concatenates but it doesn't include the separator and is not called delAdd. I have also tried CONCAT_WS and CONCAT but all are giving the same results. I am using MySQL 5.6.43 on 10.3.18-MariaDB server.
Asked by Abu Nooh (155 rep)
Sep 25, 2019, 05:34 PM
Last activity: Sep 25, 2019, 08:28 PM