0

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.

2

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 
  • Great the alias works however the separator isn't showing, any ideas? Thank you! – Abu Nooh Sep 25 at 21:43
  • @AbuNooh separator may be rendering as a line break due to HTML. Try to print it out as a character stream. – Madhur Bhaiya Sep 26 at 3:48
  • No it doesn't work, the first one seems to work fine. – Abu Nooh Sep 26 at 6:29
  • @AbuNooh can you set up a db-fiddle.com with some small sample data to play with. – Madhur Bhaiya Sep 26 at 6:33
  • After playing about with it for a while, I have directly concatenated by placing '<br>' in between the field names. – Abu Nooh Sep 27 at 18:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.