Mysql – Group_concat on IF Select MYSQL

group-concatenationmariadb-10.3MySQLmysql-5.6

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
       '<br>') 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 '<br>') 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.

Best Answer

To alias the expression computed inside a Correlated Subquery, you will need to specify it outside the complete subquery. In this case, it will be outside the If(..) block:

SELECT o.order_id,
       c.customer_name,
       Group_concat(p.product_name, ' x', p.product_quantity ORDER BY
       receipt_order ASC
       SEPARATOR
       '<br>') 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 '<br>') -- no need of aliasing here
           FROM   address a
           WHERE c.customer_id = a.customer_id
                 AND c.customer_type = a.customer_type), 
          NULL) AS delAdd  -- Aliasing needed here
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
Related Question