I have orders table where i show information about order number, products, and customer.

I need to show list of product names and their qty in column products separated or in new row like below in table:

Expected result

id    |   order_number   |  customer    |  address      |    total    |  products
------------------------------------------------------------------------------------
1     |    OR00123       |  Tom Helks   |  Test 221     |    1233,2   | 1x iphone
                                                                      | 2x samsung
                                                                      |  3x someproduct

Order table

id    |   order_number   |  customer_id  |    total    |  status_id
------------------------------------------------------------------------------------
1     |    OR00123       |    1          |   1233,2    | 1
                                                                      

OrderProducts table

  id    |   order_id   |  product_id    |  qty      |    
------------------------------------------------------
   1    |    1         |  5             |  1        |

   2    |    1         |  2             |  2        |

   3    |    1         |  6             |  3        |

Product table

  id    |   name            |  price  
----------------------------------------
   5    |    iphone         |  1231            

   2    |    samsung        |  2322             

   6    |    someproduct    |  432   

     

What i try.

I get above expected result but only dont do subquery for listing products and qty for order.

SELECT  
    order.number,
    customer.name,
    customer.adress,
    SUM(order_products.qty * product.price) as total,
    
   # subquery here
   (
      SELECT p.name FROM products p WHERE order_products.product_id = p.id
   ) as products
   
FROM.orders as order

INNER JOIN customer on customer.customer_id= customer.id
INNER JOIN order_products on order.id = order_products.order_id
ORDER BY dok.created_at;

My imagination stopped and I believe that the problem is in the sub-query, but I can't see it at the moment.

Thanks

CodePudding user response：

You need to join all tables and GROUP BY the unique values.

The GROUP_CONCAT has to be used with then CONCAT of the wanted values

CREATE TABLE orders
    (`id` int, `order_number` varchar(7), `customer_id` int, `total` varchar(6), `status_id` int)
;
    
INSERT INTO orders
    (`id`, `order_number`, `customer_id`, `total`, `status_id`)
VALUES
    (1, 'OR00123', 1, '1233,2', 1)
;

CREATE TABLE orders_product
    (`id` int, `order_id` int, `product_id` int, `qty` int)
;
    
INSERT INTO orders_product
    (`id`, `order_id`, `product_id`, `qty`)
VALUES
    (1, 1, 5, 1),
    (2, 1, 2, 2),
    (3, 1, 6, 3)
;)

Records: 3  Duplicates: 0  Warnings: 0

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1

CREATE TABLE product
    (`id` int, `name` varchar(11), `price` int)
;
    
INSERT INTO product
    (`id`, `name`, `price`)
VALUES
    (5, 'iphone', 1231),
    (2, 'samsung', 2322),
    (6, 'someproduct', 432)
;

Records: 3  Duplicates: 0  Warnings: 0

SELECT 
  order_number, `customer_id`, `total`,GROUP_CONCAT(CONCAT( op.`qty`,'x ',p.`name`) SEPARATOR  ' ') products
FROM orders o
JOIN orders_product op ON o.`id` = op.`order_id`
JOIN product p ON op.`product_id` = p.`id`
GROUP BY order_number, `customer_id`, `total`

fiddle