SQL query with subqueries, joining multiple tables - MySQL

Question

The tables are: 1) Producers: id, name 2) Products: id, name, price, category_id, producer_id 3) Customers: id, name, surname, age, country_id 4) Customer_orders: id, customer_id, product_id, quantity, discount, order_date I need to find the prodcuer, who's income was the highest, in other words: find a producer for which the sum of all prodcut price * quantity - discount is the highest. I suppose, that I should join table customer_orders with product_id column on products, and products with producers table on column id and then calculate the appropriate income for all producers and then find the maximum value. The problem is, how to write in in MySql. JavaScript questions and answers, JavaScript questions pdf, JavaScript question bank, JavaScript questions and answers pdf, mcq on JavaScript pdf, JavaScript questions and solutions, JavaScript mcq Test , Interview JavaScript questions, JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)

Answer 1

You can start with something like this: SELECT SUM(price - discount) income, producers.name name FROM customer_orders LEFT JOIN products ON customer_orders.product_id = products.id LEFT JOIN producers ON producers.id = products.producer_id GROUP BY producer_id I'm not sure if that will work exactly how I typed it but it should be a starting point. Here are some helpful links: Left Joins Sum Function Please note: I'm currently working on a server where the tables are structured like this, and it's super slow and a pain to get anything done. I'd encourage you to consider at least adding the total order about and the producer_id to the customer_orders table to make it a lot easier.