There is such a database. The table orders (Orders) and products (Products) are connected via the link table OrderProducts. DB schema I make such a request

SELECT Orders.id as NЗаказа, Products.name as НаименованиеПродукта, Products.price as Цена, OrderProducts.quantity as Количество, Products.price * OrderProducts.quantity as Стоимость FROM Orders, OrderProducts, Products WHERE OrderProducts.order_id = Orders.id AND OrderProducts.product_id = Products.id

At the exit
output table
How can I write such a request so that the cost of the order would be displayed, while there may be several items in the order?

  • one
    You would add the label of the database you are using (mysql, mssql, etc.) - Bald
  • Is this not a sql-ex task? Familiar looks) - Nick Volynkin ♦

1 answer 1

I think something like this (if you only need the order number and cost)

 SELECT Orders.id as NЗаказа, sum( Products.price * OrderProducts.quantity ) as Стоимость FROM Orders, OrderProducts, Products WHERE OrderProducts.order_id = Orders.id AND OrderProducts.product_id = Products.id group by Orders.id

plus in my opinion through left join would look more clear

 SELECT Orders.id as NЗаказа, sum( Products.price * OrderProducts.quantity ) as Стоимость FROM Orders LEFT JOIN OrderProducts on OrderProducts.order_id = Orders.id LEFT JOIN Products on OrderProducts.product_id = Products.id group by Orders.id