How to display an order with all positions in MySQL?

Question

MySQL

There is a database of four tables (user, product, order_processing, order_position). For example, user number 4 ordered several products at once in one order number 2. How to display exactly the order number, all selected products and the username using join?

Answer 1

Something like this

SELECT 
`user`.`name` AS 'USER_NAME',
`order_processing`.`idOrder` AS 'ID_ORDER',
`product`.`name` AS 'PRODUCT_NAME',
`order_position`.`amount` AS 'PRODUCT_AMOUNT'
FROM `order_position`
LEFT JOIN `product` ON `product`.`idProduct`=`order_position`.`idProduct`
LEFT JOIN `order_processing` ON `order_processing`.`idOrder`=`order_position`.`idOrder`
LEFT JOIN `user` ON `user`.`idUser`=`order_processing`.`idUser`
WHERE `user`.`idUser`=4 AND `order_processing`.`idOrder`=2;